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
Anonymous
Not applicable

How to create a descriptive satistics table?

Hi everyone!
I have a table with some fields - deliveries, sales, forecast and returns - for which I would like to create a table visual showing 3 very basic descriptive statistics: minimum, maximum and average, in a format similar to the one below:

Tabela estatísticas descritivas.png

As you can see, the fields I would like to describe appear as rows, while the statistics appear as columns. The values would change based on some slicers I already have on the report. So far, I have created a measure for each statistic for each field, but this approach doesn't allow me to create a table similar to the one above, where the minimum, maximum and average are columns of the table visual. I'm almost sure there is a better way to accomplish what I want without creating this many measures, but my DAX knowledge is limited so I couldn't yet figure out how to do it. 

Any help would be greatly appreciated.

Thanks in advance!

1 ACCEPTED SOLUTION
V-lianl-msft
Community Support
Community Support

Hi @Anonymous ,

 

Based on the sample data you provided, I created a test pbix file

Select the two columns and click unpivot other column.

test_How to create a descriptive satistics table2.PNG

Then create the measure according to your requirements and drag them into the matrix.

test_How to create a descriptive satistics table3.PNG

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
V-lianl-msft
Community Support
Community Support

Hi @Anonymous ,

 

Based on the sample data you provided, I created a test pbix file

Select the two columns and click unpivot other column.

test_How to create a descriptive satistics table2.PNG

Then create the measure according to your requirements and drag them into the matrix.

test_How to create a descriptive satistics table3.PNG

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Hey @V-lianl-msft ,

Thanks a lot for the explanation and the demo file.

At first I did what you suggested, and it worked for creating the summary table, but broke the visualizations I already had in the report, because the fields they used were no longer present. So what I did to fix this was duplicate the original query in Power Query, then do the unpivot other columns step and then create the necessary relationships, namely a one-to-many relationship between publicacao[id] and the new table[publication_id], and a many-to-many inactive relationship between the pivoted table[distribution_date] and the unpivoted_table[distribution_date], as shown below:

data model.png

Finally, to create the min, max and average metrics, I used a combination of CALCULATE, the respective metric and USERELATIONSHIP function. It was necessary to use the many-to-many relationship so that the values in the table visualization got updated according to the distribution date slicer I use in the report:

report.png

I confirmed the values in the matrix were correct by comparing them to the measures I had previously created for each attribute (entrega, forecast, venda). 

 

Best regards,

Ricardo

V-lianl-msft
Community Support
Community Support

Hi @Anonymous ,

 

Try to Unpivot the table in query editor.

test_How to create a descriptive satistics table.PNG

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

@V-lianl-msft ,

 

Which table exactly do you suggest I unpivot? This is the table on which the measures are calculated:

tabela power query.png

 

The table visual does not have fixed values, since they change based on slicers on the page. 

 

This is how the table visual looks as of now, which is not what I intend:

tabela_visual.png

The image I posted in the original post is not from Power BI, but instead from a dashboard built with R shiny.

amitchandak
Super User
Super User

@Anonymous , You have to create those measures and later split them for visual table like given below

 

Summarize table for formatted tables
	Display Table = union (SUMMARIZE(Sales," Measure", "MTD", "This",[MTD],"Last",[LMTD],"Change",[MOM]),
	SUMMARIZE(Sales," Measure", "QTD", "This",[QTD],"Last",[LQTD],"Change",[QOQ]),
	SUMMARIZE(Sales," Measure", "YTD", "This",[YTD],"Last",[LYTD],"Change",[YOY]))

 

lbendlin
Super User
Super User

Have a look at the "Show on rows"  options for your values section in the visual settings.

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.