Forum Discussion

dvhez's avatar
dvhez
Helper II
7 years ago
Solved

Average by category AND date

I'd like to calculate the average of CMg for every Bloque Horario category (A, B and C). I tried with the following measure which works only if select one month. However, my data has 10 years with 12...
Need Help
  • MariaP's avatar
    MariaP
    7 years ago

    Hi dvhez ,

    From your comment: calculate only the block (A, B or C) corresponding to each month and year?

    It now sounds like you do not need  the Year|Month|Hour  to average by but Year|Month ?

    Is this the result you are after?


    Jan 2019[ A] = 44.03 , Jan 2019 [B] = 43.0
    Feb 2019 [A] = 45.62 , Feb 2019 [B] = 44.59

    If so here is the code for this one. Note, filter context is crucial when using these calculations

    _Measure5 = 
CALCULATE (
    AVERAGE(Data[CMg] ),
    FILTER ( ALL( Data ), Data[Bloque Horario] <= MAX ( Data[Bloque Horario] )  && Data[Date] <= MAX(Data[Date])
))

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

Hi,

Share the link from where i can download your PBI file.  Also, it will be nice to also see your calculation in an Excel workbook so that i can compare my DAX formula's result with your Excel formula's result.

dvhez's avatar
dvhez
Helper II
7 years ago

Hi Ashish_Mathur , you can download the pbix from here:

 

The output logic and result would be the same as the picture I posted minutes ago.