Measure and Filter Context

[felipevaz]

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

[v-xicai]

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.

[felipevaz]

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

[felipevaz]

My data is here, @v-xicai could you try on this example? I cannot do as you explain.

Ive tried to create new columns...

The GrandTotal of Fixed Values is 59661 and they must to be always used as denominator.