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

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

Hi,

Your question is not clear.  Explain the business question, share some data and also show the expected result in MS Excel.  Also, share the link from where i can download your file.

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]
      )