cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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 the id for each month. This is my table (on the left). The expected result is the right table.

I created 2 measures to calculate the duration, but i get always the number of all id's not only the relevant od's (with duration in the selected month).

Measure 3 = calculate(DISTINCTCOUNT(source[ID]),ALL(source, source[month_date]))

Measure 4 = calculate(sum(source[Duration])/source[Measure 3])

1 ACCEPTED SOLUTION
Community Support

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

12 REPLIES 12
Community Support

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

Frequent Visitor

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

Community Support

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

Frequent Visitor

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

 55 brown 41 2022-01-31 11 green 20 2022-02-28 22 green 20 2022-02-28 33 green 20 2022-02-28 44 green 20 2022-02-28 55 green 4 2022-02-28 55 brown 45 2022-02-28 55 yellow 7 2022-02-28 11 green 43 2022-03-31 22 green 43 2022-03-31 33 green 43 2022-03-31 44 green 43 2022-03-31 11 green 54 2022-04-30 11 yellow 11 2022-04-30 22 green 54 2022-04-30 22 yellow 11 2022-04-30 33 green 64 2022-04-30 44 green 45 2022-04-30 44 brown 19 2022-04-30 5 brown 19 2022-05-31 11 yellow 33 2022-05-31 22 yellow 14 2022-05-31 22 yellow 3 2022-05-31 22 brown 16 2022-05-31 33 green 67 2022-05-31 33 brown 16 2022-05-31 33 yellow 3 2022-05-31 44 brown 22 2022-05-31 44 yellow 19 2022-05-31 5 brown 41 2022-06-30 11 yellow 55 2022-06-30 22 yellow 25 2022-06-30 33 yellow 25 2022-06-30 44 yellow 41 2022-06-30 1 green 21 2022-07-31 2 green 21 2022-07-31 3 green 21 2022-07-31 4 green 21 2022-07-31 5 green 2 2022-07-31 5 brown 43 2022-07-31 5 yellow 9 2022-07-31 22 yellow 36 2022-07-31 33 yellow 36 2022-07-31 44 yellow 52 2022-07-31 1 green 44 2022-08-31 2 green 44 2022-08-31 3 green 44 2022-08-31 4 green 44 2022-08-31 1 green 55 2022-09-30 1 yellow 12 2022-09-30 2 green 55 2022-09-30 2 yellow 12 2022-09-30 3 green 66 2022-09-30 4 green 46 2022-09-30 4 brown 20 2022-09-30 1 yellow 33 2022-10-31 2 yellow 14 2022-10-31 2 yellow 3 2022-10-31 2 brown 16 2022-10-31 3 green 68 2022-10-31 3 brown 16 2022-10-31 3 yellow 3 2022-10-31 4 brown 22 2022-10-31 4 yellow 19 2022-10-31 1 yellow 55 2022-11-30 2 yellow 25 2022-11-30 3 yellow 25 2022-11-30 4 yellow 41 2022-11-30 55 brown 20 2021-12-31

Best Regards,

reporter9

Community Support

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

Frequent Visitor

Hi @v-yueyunzh-msft ,
Thank you. If I drill to the quarter I see the sum for the phases, not the average.

Community Support

Hi , @reporter9

Thank you for your quick feedback. Based on the screenshot you provided , i can understand that the issue happens when you try to dill to the quater and show the "sum" not the "average" . The  root cause is in my "distinctcount" is wrong .

You can just also update the dax to this:

``````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],"Month_date",[Month_Date])))
return
DIVIDE(_duration,_count)``````

Then we can meet your need in the end:

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

Frequent Visitor

loooks good. Additionally always the current quarter (var _quarter_end) should not be visable. I have 'update' your proposal and I have the issue that with this the visual is blank. Where is my fault?

Thank you.

Average = var _phase = SELECTEDVALUE('Table'[Phase])
var _date =VALUES('Table'[Month_Date])
var _quarter_end = format(DATE( YEAR( TODAY() ), QUARTER( TODAY() ) * 3 + 1, 1 ) - 1,"DD/MM/YYYY")
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
&& _date < _quarter_end
) ,"ID",[ID],"Month_date",[Month_Date])))
return
DIVIDE(_duration,_count)
Community Support

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

Frequent Visitor

great support from you. I will test it and reply.
Thank you.

Memorable Member

Hi @reporter9,

You can use:

``Result Table = SUMMARIZE('Table','Table'[Phase],'Table'[Month Date],"Duration",SUM('Table'[Duration]),"Count",COUNT('Table'[ID]))``

Works for you? Mark this post as a solution if it does!
Check out this blog of mine: How to Export Telemetry Data from Azure IoT Central into Power BI

Frequent Visitor

Thanks for your reply. With this soloution it is not possible to filter the id

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.