Tableau Fixed LOD to PBI Issue with Filtering

[dreyz64]

Hello everyone,

I'm struggling with converting this Tableau fixed LOD expression into PBI.

Volume Industry = {fixed [Year], [Month], [Market], [Trade Channel], [ProductCategoryGroup]: SUM([Volume])}

So far in PBI I have converted this Tableau measure as follows:

Volume Industry =
CALCULATE(
SUMX('COT', '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])
)

My data is organized in the following way (simplified for this issue):

Date	Market	Trade Channel	Location	Product Group	Product	Volume
2019-01	Japan	Airports	Osaka	Group 1	Product 1.1	10
2019-01	Japan	Airports	Tokyo	Group 1	Product 1.2	5
2019-01	Japan	Ferries	Osaka	Group 1	Product 1.2	4
2019-01	Japan	Airports	Osaka	Group 2	Product 2.1	20
2019-02	Japan	Airports	Osaka	Group 1	Product 1.2	8
2019-02	Japan	Airports	Tokyo	Group 1	Product 1.2	1
2019-02	Japan	Ferries	Osaka	Group 2	Product 2.1	10
2019-02	Japan	Airports	Osaka	Group 1	Product 1.1	5

What I would like to have as a behavior is that for any filtering or visual that is at a level higher than Date, Market, Trade Channel, Product group, the Industry volume is basically the sum of volume.

However if we go at a lower level (example location), then the industry level will be equivalent to the industry level at Date, Market, Trade Channel, Product Group (related to the location).

I have the following issues: when I have filters on either of the fields in the 'AllExcept' close, the formula works. However, if I am filtering on a lower granularity (example: Osaka), the volume industry is calculated across all markets, TC, Product Groups in my data set (even though Osaka belongs only to Japan).

I tried to illustrate my issue below:

I feel that I am missing something that allows the formula to take into account context (e.g. if a location selected belongs to a single market, I should have an implicit filter on Market).

Thanks a lot for your help!

Cheers

[amitchandak]

@dreyz64 ,

https://dataveld.com/2018/02/17/tableau-to-power-bi-fixed-lod-expressions/

https://dataveld.com/2018/03/10/tableau-power-bi-include-level-detail-expressions/

https://visualbi.com/blogs/microsoft/powerbi/implementing-lod-tableau-power-bi-qliksense/

[dreyz64]

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

[v-shex-msft]

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]

@v-shex-msft 
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!

[v-shex-msft]

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]

@v-shex-msft 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