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(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 

 

 

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


         

3 Replies

  • SpartaBI's avatar
    SpartaBI
    Community Champion
    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


         

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi SpartaBI 

      It worked.. Thanks a lot..

       

  • 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