Forum Discussion

martin_cologne's avatar
martin_cologne
Regular Visitor
2 years ago

Calculating additional values for Matrix Visual

Hi Guys,   I need some help, how to get additional data into a matrix visual.   My source is similar to the following:   I could easily add this to a matrix like this:   But I wan...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
2 years ago

martin_cologne Sure, you could create 4 measures. For example, "current" and "last" would be:

current measure = 
  VAR __Table = FILTER('Table', [Type] = "current")
  VAR __Result = SUMX(__Table, [Value])
RETURN
  __Result



last measure = 
  VAR __Table = FILTER('Table', [Type] = "last")
  VAR __Result = SUMX(__Table, [Value])
RETURN
  __Result

You could then use this option:

 

The other option is that you create a disconnected table (no relationships) with a single column with row values of:

current

last

difference

percentage

 

Use this column as the rows for the matrix.

 

You create your same four measures such as those above. Then you create a single measure for use in the table that looks like this:

Measure to display = 
  SWITCH( MAX('Disconnected Table'[Column]),
    "current", [current measure],
    "last", [last measure],
    "difference", [difference measure],
    "percentage", [percentage measure]
  )

 

martin_cologne's avatar
martin_cologne
Regular Visitor
2 years ago

Hi Greg_Deckler ,

 

thanks a lot for your solution. If I haven't overseen something, both solutions are ignoring the product until now. Could you give me an hint, how to add it?

 

Option 1: 4 measures and use the "Show values on rows" option

In this scenrio I need to add a second filter criteria for products. Do you have an hint about the syntax?

current measure = 
  VAR __Table = FILTER('Table', [Type] = "current")
  VAR __Result = SUMX(__Table, [Value])
RETURN
  __Result

Option 2: A disconnected table of your row values and a single measure

I also need to add the product relationship here. Is it an good approach to start with an crossjoin based on the mentioned "disconnected table" in combination with distinct(table.produkt)?

Disconnected Crossjoin = CROSSJOIN(DISTINCT('Table'[Produkt]),'Disconnected Table')

PS: I need to delete the MAX function, in the following statement, to get it running. What is the purpose of the MAX function here?

Measure to display = 
  SWITCH( MAX('Disconnected Table'[Column]),
    "current", [current measure],
    "last", [last measure],
    "difference", [difference measure],
    "percentage", [percentage measure]
  )

Thanks

Martin