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

[Anonymous]

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 example. I have a table that looks like the following:

Company	Type	Value
A	Revenue	1,000
B	Revenue	2,500
A	Sales	50
B	Sales	125
A	Number	30
B	Number	75

What I would like to do is write a DAX expression that will return the Value of everything in the Type column divided by the value of Number:

For example Revenue/Number and Sales/Number for Company A and B, while respecting the filter context of the Pivot Table - there is a lot of other columns excluded in the above simple example for ease.

The above is a simplified table, so there could be 1000,s of values for Company A/B under Revenue, Sales and Number. So know I have to use sum kind of SUM and DIVIDE function, perhaps with a CALCULATE.

Any pointers to get me started would be awesome!

[v-yalanwu-msft]

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]

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?

[kitgo2]

try -- you would just need to replace Revenue with Sales for the sales measure:

#Revenue = Calculate(Divide(CALCULATE(sum(Pivot[Value]),Pivot[Type]="Revenue", Allexcept(Pivot,Pivot[Company])),CALCULATE(sum(Pivot[Value]),Pivot[Type]="Number", Allexcept(Pivot,Pivot[Company])),0),ALLEXCEPT(Pivot,Pivot[Company]))

[Anonymous]

Hi @kitgo2  thanks a lot for this, let me a give it a try and will come back to you.

[kitgo2]

try -- you would just need to replace Revenue with Sales for the sales measure:

#Revenue = Calculate(Divide(CALCULATE(sum(Pivot[Value]),Pivot[Type]="Revenue", Allexcept(Pivot,Pivot[Company])),CALCULATE(sum(Pivot[Value]),Pivot[Type]="Number", Allexcept(Pivot,Pivot[Company])),0),ALLEXCEPT(Pivot,Pivot[Company]))