Forum Discussion
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).
Can someone please help me?
Measure 3 = calculate(DISTINCTCOUNT(source[ID]),ALL(source, source[month_date]))
Measure 4 = calculate(sum(source[Duration])/source[Measure 3])
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
- Shaurya
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 BIThanks for your reply. With this soloution it is not possible to filter the id
- v-yueyunzh-msft
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
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
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
