Forum Discussion
User customizable Pivot Table
Hi,
I have a requirement in my Power BI report that I'm struggling with for days now.
My datamodel is quite large with > 20 tables. I would like a matrix where I can count the distinct amount of customers and let the user of the report in his browser dynamically add and remove dimensions as rows and/or columns in where the distinct amount of customers is grouped by the values of these dimensions.
In short: I’m searching for a way to create the Excel Pivot Table functionality J. The goal is that the user of the report can create their own custom made reports in his browser in which the value is always the distinct amount of customers, but the rows and columns in which those amount of customers are grouped can manually be selected.
Simple Example (with only one table)
ID | Office | Active | Color |
1 | GR | Yes | Red |
2 | GR | Yes | Blue |
3 | GR | No | Blue |
4 | NO | No | Blue |
5 | NO | No | Red |
6 | NO | No | Red |
7 | ZW | No | Red |
8 | ZW | No | Blue |
9 | ZW | Yes | Blue |
10 | ZW | Yes | Blue |
The User can choose the report rows and columns. For example he wishes to see the amount of customers by Color and Office as Rows and Active as a Column. The result should something like this:
Rows | Yes | No | Total |
Blue | 3 | 3 | 6 |
GR | 1 | 1 | 2 |
NO | 1 | 1 | |
ZW | 2 | 1 | 3 |
Red | 1 | 3 | 4 |
GR | 1 | 1 | |
NO | 2 | 2 | |
ZW | 1 | 1 | |
Total | 4 | 6 | 10 |
Thanks & Regards,
Tiemen
The new preview feature 'personalize visual experience' is the solution for me. Thank you for your help.
Regards,
Tiemen
5 Replies
Anonymous , use all expect
https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/
This should give you the required data
Hi amitchandak ,
Thanks for your reply. Personally I do not the relation between creating a customizable pivot table in Power BI for browser based users and using ALLEXCEPT in DAX. Can you explain how ALLEXCEPT can help me in creating the customizable pivot table?
Regards,
Tiemen
Hi Anonymous ,
Based on your expected output, you can use Matrix visual to achieve it directly:
Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
The new preview feature 'personalize visual experience' is the solution for me. Thank you for your help.
Regards,
Tiemen
Hi Anonymous ,
Glad to hear the issue is solved. You can accept your reply as solution, that way, other community members could easily find the answer when they get same issues.
Best Regards,
Yingjie Li
