Pivot a Table Visual (DAX Measure Concept Help)

[b2wise]

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!

[b2wise]

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:

1. How do I add a filter using a measure such as average sales price > 30?
2. 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

[b2wise]

@Jihwan_Kim I forgot to @ you

[Jihwan_Kim · Accepted solution]

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:]
)