Forum Discussion
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 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!
5 Replies
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.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?
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]))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]))Hi kitgo2 thanks a lot for this, let me a give it a try and will come back to you.
