Calculating additional values for Matrix Visual

[martin_cologne]

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 want to add additional calculations like difference or percentage as additional lines, like the following (taken out of excel):

What is the best way to archive this with the given data structure?

Is it usefull to add additional lines with this calcuatation?

If yes, I can a manage this?

If not, which data structure is usefull to manage the calcuation at the one hand and still able to use the matrix visual?

PS: I allready tried the following structure, but these seems to be incompatible with the matrix visual. 

Thanks a lot

Martin

[Greg_Deckler]

@martin_cologne You have two options. A disconnected table of your row values and a single measure. Or, 4 measures and use the "Show values on rows" option.

[martin_cologne]

Thank you for your super fast reply. Do you may habe some additional hints, what to do where? I'm a bit confused. 

[Greg_Deckler]

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

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