Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Divide All Values Based on Column While Respecting Filter Context in Pivot Table

  Hi all,   First post, hope everyone is well 😊   I have a relatively simple query, but unsure of how to go about constructing a DAX expression.   Probably easier to explain with a simple exa...
v-yalanwu-msft's avatar
v-yalanwu-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi, Anonymous ,

You could create a measure.

Measure = 
DIVIDE( CALCULATE(SUM([Value ]),FILTER(ALLSELECTED('Table'),[Company ]=MAX([Company ])&&[Type ]=MAX([Type ]))),
CALCULATE(SUM([Value ]),FILTER(ALL('Table'),[Company ]=MAX('Table'[Company ])&&[Type ]="Number")))

The final show:


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

     

    Thank you so much v-yalanwu-msft for this Measure! ðŸ˜€

     

    I had simplified the table significantly - there was one added complexity.

     

    Both Company and Type are seperate Fact tables that are connected to Value with keys whose grain is what I included in the example table (e.g (A,B,...) for Company and (Sales, Revenue, Number,....) for Type). 

     

    I want to be able to pivot both Company and Type using the Fact tables, that have a rolled up view of the primary keys.

     

    What change would need to be made in the measure you suggested to bring in these related tables?