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.