Issue is matrix visual

[Anonymous]

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(Fact_Cost[COGS]),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))))

And also I'm getting correct value.

But in Matrix whenever I put this as value, Other Purchase type is also showing with the value of "Import"

My requirement is only to show value of "Import", and others should come as blank.

Note: I'm unable to share pbix file as I'm woring in VM.

@amitchandak @SpartaBI @Ashish_Mathur @DataInsights @Shishir22 @Jihwan_Kim @johnt75 @speedramps 

[speedramps]

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

[SpartaBI · Accepted solution]

@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

     

[Anonymous]

Hi @SpartaBI 

It worked.. Thanks a lot..