Forum Discussion

felipevaz's avatar
felipevaz
Helper I
7 years ago

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 measu...
Need Help
v-xicai's avatar
v-xicai
Community Support
7 years ago

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.

felipevaz's avatar
felipevaz
Helper I
7 years ago

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's avatar
    felipevaz
    Helper I
    7 years ago

    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.