The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hello community,
My table contains several rich identifiers for unit sold per product per week, my problem is the third step to calculate a run rate metric. I have created the following measures so far:
Measure 1 (measure to calculate the running total of the last 13 weeks):
CALCULATE([Total Units Sold],
FILTER(ALL('Fiscal Week'),
'Fiscal Week'[Index]>= MAX('Fiscal Week'[Index])-13 && 'Fiscal Week'[Index]>=MAX('Fiscal Week'[Index])))
Measure 2 (to count the number of reported weeks of sell out in the last 13 weeks):
Reported Weeks of Sell Out = CALCULATE(COUNTA('13 Week RR'[Units Total]),
FILTER(ALL('Fiscal Week'),
'Fiscal Week'[Index]>= MAX('Fiscal Week'[Index])-13 && 'Fiscal Week'[Index]>=MAX('Fiscal Week'[Index])))
Now I want to calculate, what was the max reported weeks of sell out by product (FGA ID) by week.
The desired output is the following - consider matrix showing others weeks at the same time, this is just a sample:
How can I make the new measure, calculate only by the current week and by FGA ID, without considering the other identifiers, and showing that value for all the matrix rows within this specification.
After getting this max measure, I only will need to divide @"max measure"/"measure1" to get the run rate metric.
Help really appreciated!
@AnaCh_ , Try a measure like
CALCULATE(maxx(values('Fiscal Week'[Index]), [Total Units Sold]),
FILTER(ALL('Fiscal Week'), 'Fiscal Week'[Index]>= MAX('Fiscal Week'[Index])-13 && 'Fiscal Week'[Index]>=MAX('Fiscal Week'[Index])))
@amitchandak thank you for your quick reply,
I changed the measure whitin the MAXX, because what I need is the max of Measure2 " Reported Weeks of Sell Out". But the matrix is showing the same values as the measure 2 (Reported Weeks of Sell Out) 😞