Forum Discussion

matemusic's avatar
matemusic
Advocate III
10 years ago

Statistic data at the end of matrix

Hi, 

 

is there any way to get statistical values at the end rows of matrix table? For example, i have matrix with dimensions in rows and at the end of these values i want to get additional options for statistical values.

 

Like at the picture:

 

Maybe there is a way to show measurements as dimensions?

 

I tried with "if" function. It worked but it is to complex for bigger data.

 

I looked up for custom visuals, but there is no visualisation, that could do that. 

 

Maybe there is another way or existing visual?

Need Help

3 Replies

  • SabineOussi's avatar
    SabineOussi
    Skilled Sharer
    10 years ago

    Hello,

     

    You can put your min, max, sum, avg, sd, ... in a separate card visual, modify its title and formatting and display it at the end of your matrix.

     

    As far as I know, there is no summary table.

     

    I'm curious to know how you did it with an IF condition and append it to the table?

    • matemusic's avatar
      matemusic
      Advocate III
      10 years ago

      Sorry for late response :)

       

      For summary with if function i had to create a new table with coppied dimensions in first column, (lets call it simple "Table").

       

      I that column i coppied dimensions from original table in first columne "Category" (for example dimensions 1 to 5)  and added two additional dimensions (Dimension 6: Sum; Dimension 7: Average ).

       

      In the second column ("Values") I writed values:

      • 0 for dimensions 1 to 5
      • 1 for dimension Sum
      • 2 for dimension Average.

       

      Then I connected new table to table with original dimensions.

       

      Then I created formula 3 formulas,

      • one for calculation i wanted in Matrix. Name of formula = [Calculation]
      • one for Sum. Name of formula = [Sum]
      • one for Average. Name of formula = [Average]

      The last formula was with IF:

      Ifformula:=if(
      max(Table[Values])=1;[Sum];if(
      max(Table[Values])=2;[Average];
      [Calculation]))

       

      In Power BI then I created matrix visualisation with categories from created table (With name "Table" :)) and with IF formula.

       

      I am not sure if this formula is perfectly correct, but i hope you get the idea. This way of using IF also works for calculating values between rows. However, it has many problems:

       

      • It is to complicate, it takes too much time
      • It works pretty good in Power Pivot, but in Power BI, does not work always (maybe bugs?)
        • For example, in Power BI it works only if i don't sort data in tables.

      Greetings,

      Matej