Forum Discussion

Th0mc's avatar
Th0mc
Frequent Visitor
3 years ago

How to show a Target in a Matrix

Hi all,

 

I'm trying to show a Target inside a Matrix visual without success.

To keep it simple, here is what I have :

- one dimension table with 'Domains', one with a subdivion 'SubDomain'

- one fact table with the target by domain by month

DomainMonthValue
Domain1January

1000

.........
Domain1December900
Domain2January2000
...  
Domain2December2200
etc.  

- one fact table with projects costs

DomainSubDomainProjectCost Category1Cost Category2Cost
Domain1SubDomain1Project1Software CostEE100
Domain1SubDomain1Project1InternalNA200
...     

- Relations between tables :

- DimDomain 1-* FactTargets

- DimDomain 1-* DimSubDomain 1-* FactProjectCosts

 

My goal is to add in an existing Matrix the target of the last month in a column :

DomainCost Category 1 Value1Cost Category 1 Value2TotalTarget
Domain1xxxxxxxx 900
Domain2xxxxxxxx 2200

 

When I try to add the target to the Matrix, I always have the sum of all the targets for all the months, all the domains for each domain 😞

 

Thanks in advance

Thomas

Need Help

4 Replies

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Community Support
    3 years ago

    Hi Th0mc ,

     

    Please try following DAX:

    Target = IF(ISINSCOPE('Table1'[Domain]),CALCULATE(MAX('Table1'[Value]),FILTER('Table1','Table1'[Month] = "December")),BLANK())

     

     

    Best regards,

    Yadong Fang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Th0mc's avatar
      Th0mc
      Frequent Visitor
      3 years ago

      Thanks Yadong

      It is much better!

      But I still have some requirements 😉

       

      Following your recommandation, I used the following formulae :

       

      Target = IF(ISINSCOPE('DimDomain'[Domain]),CALCULATE(MAX('FactDomainTarget'[Value]),FILTER('FactDomainTarget', MONTH('FactDomainTarget'[Month] = 12)),BLANK())

       

      That seems to work fine. thanks again.

       

      But then, if I add it as a value in the Matrix, I get fro example :

      DomainCostCategory1 Value1TargetCostCategory1 Value 2Targetetc.... 
      Domain1 Target for Domain1 Target for Domain1  
      ....      
             
      TotalTotal of value<Blank>Total of value<Blank>  

       

      --> Target value is repeated for Cost Category value whereas I would need to see Target only once

      --> The total of Target, at the end the first thing we are looking at, is missing

       

      Any way to achieve to get only one column with the target and get the total amount of target.

       

      thanks again

      Thomas

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    Th0mc Should be something along the lines of:

    Measure =
  VAR __Domain = MAX('Table'[Domain]
  VAR __Date = MAX('Table'[Date])
  VAR __LMDate = EOMONTH(__Date,-1)
  VAR __LM = FORMAT(__LMDate,"mmmm")
  VAR __LMYear = YEAR(__LMDate)
  VAR __Table = FILTER(ALL('Table'),[Domain] = __Domain && [Month] = __LM && [Year] = __LMYear)
RETURN
  MAXX(__Table,[Value])
  • v-yadongf-msft's avatar
    v-yadongf-msft
    Community Support
    3 years ago

    Hi Th0mc ,

     

    If you want to get the total amount of target, please try:

    CALCULATE(MAX('FactDomainTarget'[Value]),FILTER('FactDomainTarget', MONTH('FactDomainTarget'[Month] = 12))

     As for the first question, please provide more details.

     

    Best regards,

    Yadong Fang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.