Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Average over SUMX

I cannot seem to get the desired average I want. The average function is giving too much weight to the group with 4 different coverage types. I'm combining this with my second question because these ...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi Anonymous 

    You could use this two formula to get the custom bins:

    Census to Bin = CALCULATE(SUM('FactPremium'[Census Total]),ALLEXCEPT('FactPremium','FactPremium'[Group Name],'FactPremium'[Coverage Type],'FactPremium'[Coverage Date]))
    Desired Bin = IF(FactPremium[Census to Bin]<=1,"",
IF(FactPremium[Census to Bin]<=14,"2-14",
IF(FactPremium[Census to Bin]<=24,"15-24",
IF(FactPremium[Census to Bin]<=99,"25-99",
IF(FactPremium[Census to Bin]<=499,"100-499",
IF(FactPremium[Census to Bin]<=999,"500-999",
"1000+"))))))

    and here is sample pbix file, please try it.

     

    Regards,

    Lin

v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
6 years ago

hi Anonymous 

You could use this two formula to get the custom bins:

Census to Bin = CALCULATE(SUM('FactPremium'[Census Total]),ALLEXCEPT('FactPremium','FactPremium'[Group Name],'FactPremium'[Coverage Type],'FactPremium'[Coverage Date]))
Desired Bin = IF(FactPremium[Census to Bin]<=1,"",
IF(FactPremium[Census to Bin]<=14,"2-14",
IF(FactPremium[Census to Bin]<=24,"15-24",
IF(FactPremium[Census to Bin]<=99,"25-99",
IF(FactPremium[Census to Bin]<=499,"100-499",
IF(FactPremium[Census to Bin]<=999,"500-999",
"1000+"))))))

and here is sample pbix file, please try it.

 

Regards,

Lin

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    This worked! Thank you!