Forum Discussion
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:
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!
Hi Anonymous ,
Based on the sample data you provided, I created a test pbix file
Select the two columns and click unpivot other column.
Then create the measure according to your requirements and drag them into the matrix.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
V-lianl-msftCommunity 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.
Then create the measure according to your requirements and drag them into the matrix.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.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:
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:
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
- lbendlin
Super User
Have a look at the "Show on rows" options for your values section in the visual settings.
- amitchandak
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])) - V-lianl-msft
Hi Anonymous ,
Try to Unpivot the table in query editor.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Which table exactly do you suggest I unpivot? This is the table on which the measures are calculated:
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:
The image I posted in the original post is not from Power BI, but instead from a dashboard built with R shiny.
