Forum Discussion

dreyz64's avatar
dreyz64
Helper III
6 years ago

Tableau Fixed LOD to PBI Issue with Filtering

Hello everyone,   I'm struggling with converting this Tableau fixed LOD expression into PBI.   Volume Industry = {fixed [Year], [Month], [Market], [Trade Channel], [ProductCategoryGroup]: SUM([Vo...
Need Help
dreyz64's avatar
dreyz64
Helper III
6 years ago

amitchandak I have already looked at these links and this is how I came up with my formula.

I need specific help on my formula not generic answers that I have already checked and implemented....

Anonymous's avatar
Anonymous
Not applicable
6 years ago

HI dreyz64,

I'd like to suggest you use the following measure formula lif it suitable to your requirement:

 

Volume Industry =
CALCULATE (
    SUM ( 'COT'[Volume] ),
    ALLSELECTED ( 'COT' ),
    VALUES ( 'COT'[Date] ),
    VALUES ( 'COT'[Market] ),
    VALUES ( 'COT'[Trade Channel] ),
    VALUES ( 'COT'[Product ),
    VALUES ( 'COT'[Product Category Group] )
)

 

If above not help, please share some dummy data to test.

BTW, You can also take a look at below blog mentioned the difference between all functions:

Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT 

Regards,

Xiaoxin Sheng

  • dreyz64's avatar
    dreyz64
    Helper III
    6 years ago

    Anonymous 
    Thank you for your answer.

    I had actually changed my formula to this and it had worked but your solution works as well so i will use yours 🙂

     

    _Volume Industry = 
    SUMX(SUMMARIZE('COT','COT'[Date].[Year],'COT'[Date].[Month],'COT'[Date].[Date], 'COT'[Year], 'COT'[Month], 'COT'[Market], 'COT'[Trade Channel],'COT'[Product Category Group]),
        CALCULATE(
            SUM('COT'[Volume]), 
            ALLEXCEPT('COT', 'COT'[Date].[Year],'COT'[Date].[Month],'COT'[Date].[Date], 'COT'[Year], 'COT'[Month], 'COT'[Market], 'COT'[Trade Channel],'COT'[Product Category Group])
        )
    )

     

    However I have a new issue now (with your formula and the one above): I want to calculate the industry volumes by Owner (to later calculate Share of Market). I tried to illustrate my issue in the image below:

     

    The first table is a simplified version of my raw data.

    The second table is the result when applying the Industry volume formula you gave me. It is correct.

    Where my issue is, is if I try to plot in a matrix the owner and calculate the industry volume. What PBI does is that it looks for each owner in which Market-Trade Channel- product group it belongs to and gives the industry volume based on this.

    However, I would like it to take the total of industry volume.

    So if owner 2 is only in airports, the industry volume should still be the sum of industry volumes in airports AND ferries, not simply ferries.

     

    Could you please help me?

    Thanks!

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      HI dreyz64,

      In fact, my formula is configurable, I already listed the categories fields with 'values' function which used as the category group.
      So if you want aggregate records that ignore specific category fields, you only need to remove not needed category fields to ignore the calculate split on that category. (for your scenario, it should calculate based on date, market, product category group fields)

      Modified formual:

      Volume Industry =
CALCULATE (
    SUM ( 'COT'[Volume] ),
    ALLSELECTED ( 'COT' ),
    VALUES ( 'COT'[Date] ),
    VALUES ( 'COT'[Market] ),
    VALUES ( 'COT'[Product Category Group] )
)

      In addition, I also add some comments on the old formula, you can check if it helps you to understand my formula.

      Volume Industry =
CALCULATE (
    SUM ( 'COT'[Volume] ),
    //keep current filter effects and interaction on COT table
    ALLSELECTED ( 'COT' ),
    //add values functions to use current category field value as condition to prevent the calculation across different field values and achieve data group effects
    VALUES ( 'COT'[Date] ),
    VALUES ( 'COT'[Market] ),
    VALUES ( 'COT'[Trade Channel] ),
    VALUES ( 'COT'[Product ),
    VALUES ( 'COT'[Product Category Group] )
)

      Regards,

      Xiaoxin Sheng

      • dreyz64's avatar
        dreyz64
        Helper III
        6 years ago

        Anonymous Hi again.
        thanks a lot for your explanations. It helps a bit. I am quite struggling with the VALUES, ALLSELECTED, ALL functions in general.

        I've tested again in my report and found the following:

         

        At a high level of aggregation I get the same results using:

        FORMULA 1:

        Volume Industry =
CALCULATE (
    SUM ( 'COT'[Volume] ),
    ALLSELECTED ( 'COT' ),
    VALUES ( 'COT'[Date] ),
    VALUES ( 'COT'[Market] ),
    VALUES ( 'COT'[Product Category Group] )
)

        FORMULA 2:

        Volume Industry =
SUMX(SUMMARIZE('COT','COT'[Date].[Year],'COT'[Date].[Month],'COT'[Date].[Date], 'COT'[Year], 
               'COT'[Month], 'COT'[Market], 'COT'[Trade Channel],'COT'[Product Category Group]),
     CALCULATE(
            SUM('COT'[Volume]), 
            ALLEXCEPT('COT', 'COT'[Date].[Year],'COT'[Date].[Month],'COT'[Date].[Date], 
                      'COT'[Year], 'COT'[Month], 'COT'[Market], 
                      'COT'[Trade Channel],'COT'[Product Category Group])
        )
    )

         

        However, when I trie to plot for instance against a product category, FORMULA 2 works but FORMULA 1 does not. However if I filter then they both work.

         

        In the end I have gone with the following approach: I use FORMULA 2 and when I need to filter (for isntance adding a legend) I use this:

        CALCULATE([_Volume Industry], ALLSELECTED('COT'[TOP TMOs]), ALlSELECTED('COT'[TMO]))
         
        Not sure it is the best way