Forum Discussion

Gumberculeez's avatar
Gumberculeez
Icon for Helper II rankHelper II
7 years ago

SUM based on month

Hey Guys;

 

I'm having issues wrapping my head around this sum issue I'm having.  playing with SQL data and when I leave my Total as "Don't Summarize", it pulls through the monthly revenue for that department...

 

However, as soon as I SUM the total, it ignores the date range and gives me a SUM total of all records with that department ID.  

 

Any ideas guys?

 

 

   

Need Help

5 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    7 years ago

    Hi Gumberculeez ,

     

    Calculations in PowerBI (DAX) are made based on context, so looking at your print screen the information you have on your data is AccountName and Total so for calculation of the sum of Total the only context is the AccountName so what happens is that the final result is the total for all the values of AccountName.

     

    If you want to have the calculation per month you need to also add the date to the visual in order to get the context of the month.

     

    When you select the Don't summarize option what you are asking is to return the full data for Total column that's why you see all the information per month, again, this is the context that you are giving to your data in this case to not summarize your information with an aggregator.

     

    If you need any further assistance or explanation please tell me.

     

    Regards,

    MFelix

     

    • Gumberculeez's avatar
      Gumberculeez
      Icon for Helper II rankHelper II
      7 years ago

      Thanks for that :)... helping a bit.  My underlying issue remains however.  

       

      When pick a specific account number it filters only those transactions, however, the total at the bottom remains the overall total for the month.

       

      I'd like only the filtered number total to appear.