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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

My apologies, as I am new to this forum, but I did not see where I could attach a file, which is why I just posted the screen shots. Would you know how I could do this?

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
6 years ago

Hi,

Upload the workbook to Google Drive and share the download link.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I appreciate you taking the time to look, but I was able to solve the problem by using the following:
    AVERAGEX(
        KEEPFILTERS(
            CROSSJOIN(
                VALUES(FactPremium[Group Name]),
                VALUES(FactPremium[Coverage Date])
            )
        ),
        [Census Total Average]
    )

      • 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