Forum Discussion

evanderpoel's avatar
evanderpoel
Frequent Visitor
5 years ago
Solved

Place Text Values into a Matrix Table - PLEASE HELP!

I'm wondering if there is a way to put text data into a row in a matrix. I would like to add a row that shows each price that is connected to the sub-brand on each week. Some are regular prices like $5.49 and others are multiple price points like 2/$6 or 2/$7. I used the formula below to get the data to show up, but it lists every price for the sub brand, not just the one that aligns to each week. Formula, tables I'm using, how they are connected (in blue), and current and desired outcomes are below. Thank you in advance for any direction you can provide, this is driving me nuts!!

 

 

 

 

 

 

 

 

 

Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    Hi evanderpoel ,

     

    Based on the information you shared you need to use the following syntax:

     

    MEASURE = 
IF (
    ISINSCOPE ( 'Data Master'[Week] ),
    CALCULATE (
        CONCATENATEX (
            VALUES ( 'Retail Survey'[RS Price TY] ),
            'Retail Survey'[RS Price TY],
            ","
        ),
        KEEPFILTERS ( 'Data Master' ),
        'Retail Survey'[RS Price TY] <> BLANK ()
    )
)

     

     

     

    The INSCOPE part is to remove the calculation from the total values and the keepfilters allows to keep the subbrand and week filters to get the correct prices.

3 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi evanderpoel ,

     

    You need to create a filter on the subbrand.

     

    Try somehting similar to:

     

    MEASURE = CALCULATE (
    CONCATENATEX (
        VALUES ( 'Retail Survey'[RS Price TY] ),
        'Retail Survey'[RS Price TY],
        ","
    ),
    FILTER (
        ALL ( 'Item Master'[Sub Brand] ),
        'Item Master'[Sub Brand] = SELECTEDVALUE ( 'Item Master'[Sub Brand] )
    )
)

     

    If this does not work can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

    If the information is sensitive please share it trough private message.

    • MFelix's avatar
      MFelix
      Super User
      5 years ago

      Hi evanderpoel ,

       

      Based on the information you shared you need to use the following syntax:

       

      MEASURE = 
IF (
    ISINSCOPE ( 'Data Master'[Week] ),
    CALCULATE (
        CONCATENATEX (
            VALUES ( 'Retail Survey'[RS Price TY] ),
            'Retail Survey'[RS Price TY],
            ","
        ),
        KEEPFILTERS ( 'Data Master' ),
        'Retail Survey'[RS Price TY] <> BLANK ()
    )
)

       

       

       

      The INSCOPE part is to remove the calculation from the total values and the keepfilters allows to keep the subbrand and week filters to get the correct prices.

      • evanderpoel's avatar
        evanderpoel
        Frequent Visitor
        5 years ago

        This worked great, thank you so much for your help, much appreciated!!