Forum Discussion
Measure and Filter Context
Hi, :smileysad:
I did a measure that calculate the Sum of Distinct Values and I having issues while using that measure as a denominator. I dont know how to change the filter context os that measure.
To calculate the sum of FixedValue (without duplicates) I did:
MaxOfFixedValue:=MAX([FixedValue])
SumFixedValue:=SUMX(DISTINCT(DataExample[Subcategory]);[MaxOfFixedValuye])
To calculate the sum of DynamicValue I did:
SumOfDynamicValue:=SUM([DynamicValue])
What I need: AveragePerGrandTotal (SumOfDynamicValue per Subcategory/SumFixedValue)
How Im doing: DIVIDE([SumOfDynamicValue];[SumFixedValue]
But, my desire is to calculate the SumOfDynamicValue per Subcategory divided by SumFixedValue (Simple Average). But I cant! OMG
Data example
Category | Subcategory | DynamicValue | FixedValue |
a | aa | 12 | 16 |
a | bb | 13 | 16 |
a | cc | 11 | 16 |
b | aa | 12 | 20 |
b | bb | 11 | 20 |
b | cc | 12 | 20 |
c | aa | 11 | 24 |
c | bb | 12 | 24 |
c | cc | 12 | 24 |
3 Replies
Hi felipevaz ,
You can create columns using DAX below.
SumFixedValue = CALCULATE(SUM(DataExample[FixedValue]),FILTER(ALLSELECTED(DataExample),DataExample[Subcategory]=EARLIER(DataExample[Subcategory])))
SumOfDynamicValue = CALCULATE(SUM([DynamicValue]),FILTER(ALLSELECTED(DataExample),DataExample[Subcategory]=EARLIER(DataExample[Subcategory])))
Divide = DIVIDE([SumOfDynamicValue],[SumFixedValue])
Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EfwXUnVLxzFLgkHPKk2UeOcBO2VbjtEW_-pdA7LAyzl7Ww?e=O7eoae
Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
The expected result using the Data Example:
SumOfFixedValue: 16+20+24 = 60
Final Result
SubCategory
DynamicValue
Average(DynamicValue/SumOfFixedValue) aa
36
36/60 = 0,60
bb
35
35/60 = 0,58
cc
36
36/60 = 0,60
