Equivalence of doing a pivot table of a pivot table

[analytical_mind]

Hi,

So my dataset consists of Customers, Market Category, Year

Customer	Market Category	Year
A	X	2022
A	Y	2023
B	Y	2022
D	Z	2021

I want to be able to answer the following questions: how many categories are a unique customer transactioning (but for me it doesn't matter the name of the customer, I want a summary of that): so something like

Distinct Category	Number of Unique Customers
1	2 (Customer B + Customer D, they are each transaction in a single [but different] cattegory)
2	1 (Customer A has 2 different categories)

I would achieve this in excel by doing a pivot table, selecting the customer as a row and counting the distinct categories as a Value. Then I would copy this result, paste in another sheet and do another pivot table: this time selecting the distinct category as a row and counting the number of distinct customers.

How to achieve this in DAX? I tried using summarizecolumns but when I include the year, the results don't really make sense.

SUMMARIZECOLUMNS (

    table[Customer ],

    table[Year],

    "Distinct Category", [Distinct Category] -- this is a measure in my original table; it is the distinct count of category

)

[v-yueyunzh-msft · Accepted solution]

Hi , @analytical_mind 

According to your description, you want to create a summary table by dax code .

This is my test data and my understand for your need:

We can click "New Table"  to create the calculated table :

Table 2 = var _t =  SUMMARIZE('Table','Table'[Customer],'Table'[Year] , "Distinct Category", DISTINCTCOUNT('Table'[Market Category]))

return
SUMMARIZE(_t , [Distinct Category] , [Year] , "Number of Unique Customers", DISTINCTCOUNT('Table'[Customer]))

Table 3 = var _t =  SUMMARIZE('Table','Table'[Customer],"Distinct Category",DISTINCTCOUNT('Table'[Market Category]))

return
SUMMARIZE(_t , [Distinct Category] , "Number of Unique Customers" ,  DISTINCTCOUNT('Table'[Customer]))

The result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[analytical_mind]

That's exactly what I needed, thanks. But the 'bad' part is that apparently I'll need to manage multiple summarized tables to get different analysis right?

[v-yueyunzh-msft]

Hi, @analytical_mind 

Yes, If you need to get different values under different dimensions, you really need to create two tables to get.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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