Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
matemusic
Advocate III
Advocate III

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
Skilled Sharer
Skilled Sharer

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?

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.