Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I have this table with the daily SaleAmount for each Stoare for 1 full year
Date Store SaleAmount
01/01/2020 St1 90
01/01/2020 St2 1500
01/01/2020 St3 90
02/01/2020 St1 100
02/01/2020 St2 700
02/01/2020 St3 300
....
31/12/2020 St3 1470
I'm trying to write a measure that calculates number of weeks in which each store sold more than 3000 per each week of the year called GoodWeeks.
So, if I am to build a table with the list of stores and this measure the result should be something like this
Store No of GoodWeeks
St 1 32
St 2 45
St 3 21
I would prefer not to add any column to my table
Thanks
PS: The model has also a Calendar table with an already calculated column WeekNo
Solved! Go to Solution.
Please try a measure expression like this, replace Table with your Sales table name. I also summarized over the Store too, so your total should also be correct.
Weeks Over 3000 =
VAR vSummary =
ADDCOLUMNS (
SUMMARIZE (
Table,
Calendar[WeekNo],
Table[Store]
),
"cSales",
CALCULATE (
SUM ( Table[Sale Amount] )
)
)
RETURN
COUNTROWS (
FILTER (
vSummary,
[cSales] >= 3000
)
)
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
I would have expected that measure to work even within a Year context (if the Year column is also from your Calendar table). Can you show where it broke down or explain further? Adding a link (OneDrive/Google Drive/etc) to your pbix (with mock data) would be best way to help troubleshoot.
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Please try a measure expression like this, replace Table with your Sales table name. I also summarized over the Store too, so your total should also be correct.
Weeks Over 3000 =
VAR vSummary =
ADDCOLUMNS (
SUMMARIZE (
Table,
Calendar[WeekNo],
Table[Store]
),
"cSales",
CALCULATE (
SUM ( Table[Sale Amount] )
)
)
RETURN
COUNTROWS (
FILTER (
vSummary,
[cSales] >= 3000
)
)
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thanks Pat for the quick answer. It works for this example!
I have another question: If I need to use the measure in a context in which I use year as a column filter, it doesn't seem to work anymore.
Can I update the measure to corectly calculate the figures in a context of another filter (like split by Year)?
For example:
2020 2021
Store No of GoodWeeks No of GoodWeeks
St 1 32 5
St 2 45 3
St 3 21 2
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |