Forum Discussion

reporter9's avatar
reporter9
Frequent Visitor
3 years ago
Solved

Average per month group by

Hello, I need the average of duration per month for the colors. It should be possible to filter the id. To calculate the average I need the sum for each month and color divide by distinctcount from ...
Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi, reporter9 

    Hi,

    Thank you for your quick response, I check your dax and find that the reason that causes this issue is that “format” funmction.

    Because the “format” function return the “Text” type data , we can not compare the “Text” type data to the “Date” type data. And the other error is that “ _date < _quarter_end”. We can not use the _date as the condition, we need use the ‘Table’[month_date] because we are filtering the ‘Table’.

    So in the end , you can try to user this dax:

    Average2 = var _phase = SELECTEDVALUE('Table'[Phase])

var _date =VALUES('Table'[Month_Date])

var _quarter_end = DATE( YEAR( TODAY() ), QUARTER( TODAY() ) * 3 + 1, 1 ) - 1

var _duration =SUMX(FILTER( ALLSELECTED('Table'), 'Table'[month_date] in _date && 'Table'[Phase]=_phase) , [Duration])

var _count =COUNTROWS(DISTINCT(SELECTCOLUMNS( FILTER(ALLSELECTED('Table'),

'Table'[month_date] in _date

&& 'Table'[Month_Date]< _quarter_end

) ,"ID",[ID],"Month_date",[Month_Date])))

return

DIVIDE(_duration,_count)

     

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

v-yueyunzh-msft's avatar
v-yueyunzh-msft
Icon for Community Support rankCommunity Support
3 years ago

Hi , reporter9 

Here are the steps you can refer to :
(1)This is my test data :

(2)We can create a measure :

 

Average = var _phase = SELECTEDVALUE('Table'[Phase])
var _date =SELECTEDVALUE('Table'[month_date])
var _duration =SUMX(FILTER( ALLSELECTED('Table'), 'Table'[month_date]=_date && 'Table'[Phase]=_phase) , [Duration])
var _count =COUNTROWS(DISTINCT(SELECTCOLUMNS( FILTER(ALLSELECTED('Table'),'Table'[month_date]=_date) ,"ID",[ID])))
return
DIVIDE(_duration,_count)

 

(3)Then we can put the fields we need on the visual and we will meet your need:

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

 

reporter9's avatar
reporter9
Frequent Visitor
3 years ago

First of all thank You. It seems it works for the month ( test are not finished) but if I drill up to the quarter and select serveral id's then I get sometimes a blank visual

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi, reporter9 

    For your needs, can you describe in detail how you " drill up to the quarter " and what fields you put on the visual and can you provide the end result you want ?

     

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • reporter9's avatar
      reporter9
      Frequent Visitor
      3 years ago

      Hi v-yueyunzh-msft ,

      Thank you for your reply. I have added the file with the example. Here you can see my problem.

      Sorry, it is not possible for me to add the example. Here ist the screenshot and the table.

       

       

      ID      Phase  Duration Month_Date

      55brown412022-01-31
      11green202022-02-28
      22green202022-02-28
      33green202022-02-28
      44green202022-02-28
      55green42022-02-28
      55brown452022-02-28
      55yellow72022-02-28
      11green432022-03-31
      22green432022-03-31
      33green432022-03-31
      44green432022-03-31
      11green542022-04-30
      11yellow112022-04-30
      22green542022-04-30
      22yellow112022-04-30
      33green642022-04-30
      44green452022-04-30
      44brown192022-04-30
      5brown192022-05-31
      11yellow332022-05-31
      22yellow142022-05-31
      22yellow32022-05-31
      22brown162022-05-31
      33green672022-05-31
      33brown162022-05-31
      33yellow32022-05-31
      44brown222022-05-31
      44yellow192022-05-31
      5brown412022-06-30
      11yellow552022-06-30
      22yellow252022-06-30
      33yellow252022-06-30
      44yellow412022-06-30
      1green212022-07-31
      2green212022-07-31
      3green212022-07-31
      4green212022-07-31
      5green22022-07-31
      5brown432022-07-31
      5yellow92022-07-31
      22yellow362022-07-31
      33yellow362022-07-31
      44yellow522022-07-31
      1green442022-08-31
      2green442022-08-31
      3green442022-08-31
      4green442022-08-31
      1green552022-09-30
      1yellow122022-09-30
      2green552022-09-30
      2yellow122022-09-30
      3green662022-09-30
      4green462022-09-30
      4brown202022-09-30
      1yellow332022-10-31
      2yellow142022-10-31
      2yellow32022-10-31
      2brown162022-10-31
      3green682022-10-31
      3brown162022-10-31
      3yellow32022-10-31
      4brown222022-10-31
      4yellow192022-10-31
      1yellow552022-11-30
      2yellow252022-11-30
      3yellow252022-11-30
      4yellow412022-11-30
      55brown202021-12-31

       

      Best Regards,

      reporter9

      • v-yueyunzh-msft's avatar
        v-yueyunzh-msft
        Icon for Community Support rankCommunity Support
        3 years ago

        Hi , reporter9 

        For your need , we just need to update the measure to this and we will meet your need:

        Average = var _phase = SELECTEDVALUE('Table'[Phase])
var _date =VALUES('Table'[Month_Date])
var _duration =SUMX(FILTER( ALLSELECTED('Table'), 'Table'[month_date] in _date && 'Table'[Phase]=_phase) , [Duration])
var _count =COUNTROWS(DISTINCT(SELECTCOLUMNS( FILTER(ALLSELECTED('Table'),'Table'[month_date] in _date) ,"ID",[ID])))
return
DIVIDE(_duration,_count)

        The result is as follows:

        Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

         

        Best Regards,

        Aniya Zhang

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly