Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Community,
I have a list of Order IDs on my orders table. Counting these up as distinct IDs (so I do not count the same Order ID over multiple order lines) is easy. I have done that already. What I am trying to do now is to get an average of that count, at different granulatries.
For example: I have 3400 unique orders in a year.
What I am struggling with is how to get an average, by month, week, day that I could use in a card visual. Or on three separate card visuals. For example, three card visuals: Average Orders Per Day: 23 Average Orders Per Week: 77 Average Orders Per Month: 303 (just making up numbers)
Proving to be more difficult than I thought. I have a date table connected to my orders table.
Thanks in advance for any guidance!
Solved! Go to Solution.
I think it is better combined
Per month = Averagex(summarize(Calendar, Calendar[Year], Calendar[Month]),[DistinctMeasure])
Per week =
Averagex(
summarize(Calendar, Calendar[Year], Calendar[WeekNum]),[DistinctMeasure])
@Anonymous - This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.
Is this suitable to your task?
Average orders per day =
DIVIDE(
DISTINCTCOUNT ( Table[OrderID]), COUNTROWS(Calendar)
)
And for weeks it is
Average orders per week =
DIVIDE(
DISTINCTCOUNT ( Table[OrderID]), COUNTROWS(SUMMARIZE(Calendar, Calendar[Year], Calendar[Week]))
)
You can use AVERAGEX like this
Daily Average = AVERAGEX(VALUES('Date'[Date]), [DistinctCountMeasure])
Monthly Average = AVERAGEX(VALUES('Date'[Month]), [DistinctCountMeasure])
Note that this has to reference and existing [Measure]. If you put just DISTINCTCOUNT(Table[Column]), you would have to wrap it in a CALCULATE().
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
I think it is better combined
Per month = Averagex(summarize(Calendar, Calendar[Year], Calendar[Month]),[DistinctMeasure])
Per week =
Averagex(
summarize(Calendar, Calendar[Year], Calendar[WeekNum]),[DistinctMeasure])
@hohlick @mahoneypat @Greg_Deckler Everyone...thank you for the quick responses and help. Much appreciated!
In the end I went with the solutions like this from @hohlick and they worked just fine for what I needed. Putting day, week, and monthly averages on their own distinct card visuals.
Using this as the measure inside of the formulas provided:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |