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 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's avatar
    martin_cologne
    Regular Visitor
    2 years ago

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