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.
Hi All,
I'm facing an issue in Matrix. May be I'm lacking in some tips and tricks.
I want to calculate last 90 days average COGS where purchase type is "Import"
My dax is =
Note: I'm unable to share pbix file as I'm woring in VM.
@amitchandak @SpartaBI @Ashish_Mathur @DataInsights @Shishir22 @Jihwan_Kim @johnt75 @speedramps
Solved! Go to Solution.
@Rimi_2095 try this:
My dax is =
(CALCULATE(SUM(Fact_Cost[COGS]),KEEPFILTERS(Dim_Article[PurchaseType]="Import"),DATESINPERIOD(Dim_Date[Date],MAX(Dim_Date[Date]),-91,DAY)) /(CALCULATE(DISTINCTCOUNT(Fact_Sales[Date]),DATESINPERIOD(Dim_Date[Date],MAX(Dim_Date[Date]),-91,DAY))))
I recommend that you do some online training about CALCULATE and FILTER.
Click here for free training videos
If I have a list of cat and value in ttable visual
A=10
B=22
C=33
And use TotalBcalculate = CALCULATE( SUM(facts[value]), facts[cat] = B”)
the it will return 22 for every row because CALCULATE “overrides” the visual natural grid context
Whereas
TotalBfilter =
// this gets a subset of just rows B
VAR mysubset = FILTER(table, facts[cat] = "B”)
// this sums just the subset
RETURN
CALCULATE(SUM(facts[value]), mysubset)
So your answer try this …
My dax is =
// get a subset (please note you can test and view this subset with create table !!)
VAR mysubset =
FILTER(Fact_Sales,
DATESINPERIOD(Dim_Date[Date], MAX(Dim_Date[Date]),-91,DAY))
RETURN
DIVIDE(
CALCULATE(SUM(Fact_Cost[COGS]), mysubset),
CALCULATE(DISTINCTCOUNT(Fact_Sales[Date]), mysubset)
)
Please click thumbs up and accept as solution button. Thanks
@Rimi_2095 try this:
My dax is =
(CALCULATE(SUM(Fact_Cost[COGS]),KEEPFILTERS(Dim_Article[PurchaseType]="Import"),DATESINPERIOD(Dim_Date[Date],MAX(Dim_Date[Date]),-91,DAY)) /(CALCULATE(DISTINCTCOUNT(Fact_Sales[Date]),DATESINPERIOD(Dim_Date[Date],MAX(Dim_Date[Date]),-91,DAY))))
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.