cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
matemusic Regular Visitor
Regular Visitor

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:

statistic problem.png

 

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?

3 REPLIES 3
SabineOussi Established Member
Established Member

Re: Statistic data at the end of matrix

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?

SabineOussi Established Member
Established Member

Re: Statistic data at the end of matrix

matemusic Regular Visitor
Regular Visitor

Re: Statistic data at the end of matrix

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

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Kudoed Authors (Last 30 Days)