How to show a Target in a Matrix

[Th0mc]

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

Domain	Month	Value
Domain1	January	1000
...	...	...
Domain1	December	900
Domain2	January	2000
...
Domain2	December	2200
etc.

- one fact table with projects costs

Domain	SubDomain	Project	Cost Category1	Cost Category2	Cost
Domain1	SubDomain1	Project1	Software Cost	EE	100
Domain1	SubDomain1	Project1	Internal	NA	200
...

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

Domain	Cost Category 1 Value1	Cost Category 1 Value2	Total	Target
Domain1	xxxx	xxxx		900
Domain2	xxxx	xxxx		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

[v-yadongf-msft]

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.

[v-yadongf-msft]

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]

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 :

Domain	CostCategory1 Value1	Target	CostCategory1 Value 2	Target	etc....
Domain1		Target for Domain1		Target for Domain1
....
Total	Total 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]

@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])