Forum Discussion

dreyz64's avatar
dreyz64
Icon for Helper III rankHelper 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
    Icon for Helper III rankHelper 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
        Icon for Helper III rankHelper 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!