Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
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 |
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/EfwXUnVLxzFLgkHPKk...
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |