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
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