cancel
Showing results for
Did you mean:
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:

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

Established Member

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

Announcements