Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Issue is matrix visual

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 =  (CALCULATE(SUM(Fa...
Need Help
Show and Tell
Tips and Tricks
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    Anonymous 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))))

     


    Showcase Report – Contoso By SpartaBI


         

speedramps's avatar
speedramps
Super User
4 years ago

HI Rimi_2095

 

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