Forum Discussion
Pivot a Table Visual (DAX Measure Concept Help)
Hi all,
I find myself many times putting together a table visual in Power BI and then exporting it to Excel so I can pivot and find some more insights. Instead of exporting to excel I'd like to stay in Power BI and pivot right there, so to speak.
Example below.
| Product Category | Sales (measure) | distinct products sold (measure) | Avg sales price (measure) |
| dog food | 1000 | 12 | 30 |
| cat food | 2000 | 17 | 25 |
| fish food | 300 | 6 | 5 |
| dog toys | 5000 | 17 | 15 |
| aquarium parts | 1000 | 12 | 17 |
This is a summary table of sales by product category, along with three measures. I'd like to group by "distinct products sold" (evaluated by "Product Category") and sum sales that way. I cannot do that however because "distinct products sold" is a measure not a static field.
To do this, I export to excel so I can pivot and group by "distinct products sold" and the value is the Sum of "Sales".
How can I do this in Power BI? I think the answer is using CALCULATETABLE or SUMMARIZE but I don't know exactly how to go about it.
Thanks in advance!
Hi,
I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file if it suits your requirement.
Sales measure: = SUM( Sales[Sales] )distinct product sold: = COUNTROWS(SUMMARIZE(Sales, 'Calendar'[Date]))expected result measure: = SUMX ( FILTER ( SUMMARIZE ( Sales, Category[Product Catetory] ), [distinct product sold:] = SELECTEDVALUE ( 'Product Sold distinct count CT'[Count] ) ), [Sales measure:] )
3 Replies
Hi,
I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file if it suits your requirement.
Sales measure: = SUM( Sales[Sales] )distinct product sold: = COUNTROWS(SUMMARIZE(Sales, 'Calendar'[Date]))expected result measure: = SUMX ( FILTER ( SUMMARIZE ( Sales, Category[Product Catetory] ), [distinct product sold:] = SELECTEDVALUE ( 'Product Sold distinct count CT'[Count] ) ), [Sales measure:] )You, sir, are a kind and generous genius! Thank you very much!
If you have patience, I have a couple more questions on the expected result measure:
- How do I add a filter using a measure such as average sales price > 30?
- I don't feel like my original problem is a very common one. What are other ways to solve the same problem? I'm thinking I could have created my table visual as a regular table in Power Query though it would be pretty difficult to calculate all those DAX measures.
In short, you have answered my question and I am very grateful!
Thank you again
Jihwan_Kim I forgot to @ you
