The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi Community,
Hope you all have a nice Power Bi weekend 😉 I'm trying to solve an issue most probably very easy for experienced people but after trying and trying I can't figure out how to solve it...
Quick summary of my setup:
I have a table containing all purchases:
- Purchase date
- Purchase invoice number
- Purchase category id
I have another table containing all categories
- Category id
- Category name
Relation between both tables on "category id"
During my research I discovered that I needed a Calendar table. So I created one using PowerQuery tutorial I found online. I now have a new table Calendar
- Calendar date
+ different columns to keep date only, month only ...
I created a relation between the Calendar table and the Purchases table on date
On my report I have a slicer to reduce the period to take into account. (for instance only June, July, whole year ...)
In the Purchases table I created a new measure to calculate the average of purchases per day for the number of days in the selected slicer period.
Average Daily Purchases = AVERAGEX(VALUES(Purchases[Date]), CALCULATE(COUNT(Purchases[Invoice Number])))
Good news, this seems doing the job and giving me the average of daily purchases for the period.
Sample data:
20230701 Cat1 1
20230701 Cat1 2
20230701 Cat1 3
20230702 Cat1 4
20230702 Cat2 5
20230715 Cat1 6
20230718 Cat2 7
Unfortunately, the result that I have today looks good if I have some purchases every single day of the selected slicer period. But if I have days without purchases then it gives a wrong daily average. (CAT2 gives me 2 daily average for July while I expect total purchases 2 divided by total days from the slicer selection, with current month last day = today)
I would like to create a visual showing for the selected slicer period per category the average of purchases per day.
I played with Summarize in my measure to fill the date gaps... but after hours of searching I'm completly lost.
I really hope it's clear enough and that someone will be able to help.
Appreciate your help
Chris
Solved! Go to Solution.
Is this what you are looking for?
Average Daily Reports =
VAR _CountDay = COUNTROWS(Calendar_PowerQuery)
RETURN
DIVIDE(CALCULATE(COUNT(Purchases[Category Id])),_CountDay,0)
your measure is not working due to date hierarchy
AVERAGEX(SUMMARIZE('Calendar_PowerQuery',Calendar_PowerQuery[Date]),[Count])
you need to turn it off
then you need to create another month-year column for the slicer (mmm-yy)
Is this what you are looking for?
Average Daily Reports =
VAR _CountDay = COUNTROWS(Calendar_PowerQuery)
RETURN
DIVIDE(CALCULATE(COUNT(Purchases[Category Id])),_CountDay,0)
Ahmedx,
Thank you so much for your response, energy, time, patience... exactly what I was looking for !
I've looked at some online tutorials about Calendar tables and I've unticked auto date/time, implemented a new Caledar table template for my future reports. It really makes the difference.
Have a nice Sunday and take care
Chris
Share sample pbix file to help you.
Average Daily Purchases = AVERAGEX(SUMMARIZE('Calendar',Calendar[date]), CALCULATE(COUNT(Purchases[Invoice Number])))
Unfortunately it still gives me the same results. That's what I'm dealing with for the latest 6h, always something wrong or giving me the same result whatever what I'm changing in the measure. I'm a noob, first report, so I hope I did not do something wrong somewhere else.
pls try this
Average Daily Purchases = AVERAGEX(SUMMARIZE('Purchases',Purchases[Date]), CALCULATE(COUNT(Purchases[Invoice Number])))
Hi Ahmedx,
Thanks for you quick reponse, that also something that I have already tried, but strange enough it gives exactly the same result as without the Summarize 😞
I though that maybe something was wrong with my Calendar table and the relationship. Not sure how I can check, when I create a table visual and I add Calendar[date] and Purchases[date] it shows both columns. Was not the case in the begin because of date and datetime format.