Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
Essentially I would like to be able to get the percentage of a unique variable in a column (Type) over a group defined by another column (Level 1 or Level 2). Much like the percentage that is displayed by a pie chart.
This is my hypothetical table, Level 1 and Level 2 are the categorical variables in which I would like to group by, and the percentage should be calculated based on the Type column.
The rest of the columns are all values that should be calculated using DAX, but I ran into many difficulties trying to get those figures. Would really appreciate some help here.
Ultimately, I would like to dynamically create a column that outputs the percentage of 'Type' depending on what the users selects on:
1. Which type he would like to compute the percentage on, for example, 'Malaysia', or 'India'
2. The level of hiarachy that is selected
Thanks in advance!
Level 1 | Level 2 | Type | Number of Malaysia in Level 1 Group | Total number of Type in Level 1 | Proportion of Malaysia in Level 1 | Number of Malaysia in Level 2 Group | Total number of Type in Level 1 | Proportion of Malaysia in Level 2 |
A | A | China | 2 | 4 | 50.00% | 0 | 2 | 0.00% |
A | A | China | 2 | 4 | 50.00% | 0 | 2 | 0.00% |
A | B | Malaysia | 2 | 4 | 50.00% | 2 | 2 | 100.00% |
A | B | Malaysia | 2 | 4 | 50.00% | 2 | 2 | 100.00% |
B | C | India | 1 | 4 | 25.00% | 0 | 1 | 0.00% |
B | D | China | 1 | 4 | 25.00% | 0 | 2 | 0.00% |
B | D | China | 1 | 4 | 25.00% | 0 | 2 | 0.00% |
B | E | Malaysia | 1 | 4 | 25.00% | 1 | 2 | 50.00% |
C | E | Taiwan | 0 | 4 | 0.00% | 0 | 2 | 0.00% |
C | F | Taiwan | 0 | 4 | 0.00% | 0 | 2 | 0.00% |
C | F | Norway | 0 | 4 | 0.00% | 0 | 2 | 0.00% |
C | G | Norway | 0 | 4 | 0.00% | 0 | 1 | 0.00% |
Solved! Go to Solution.
Hi @Anonymous
Create a new table without any relationship to other tables,
filter table = VALUES(Sheet1[Type])
Add Type column from "filter table" to the slicer,
Add measures in Sheet1,
number selected = CALCULATE( COUNT(Sheet1[Type]),FILTER(Sheet1,Sheet1[Type]=SELECTEDVALUE('filter table'[Type]))) total number = COUNT(Sheet1[Type]) Proportion% = [number selected]/[total number]
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
Create a new table without any relationship to other tables,
filter table = VALUES(Sheet1[Type])
Add Type column from "filter table" to the slicer,
Add measures in Sheet1,
number selected = CALCULATE( COUNT(Sheet1[Type]),FILTER(Sheet1,Sheet1[Type]=SELECTEDVALUE('filter table'[Type]))) total number = COUNT(Sheet1[Type]) Proportion% = [number selected]/[total number]
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Can you upload sample data, output expected, pbix to Google / One Drive and share the link here.
From the sample data set is it that you always calculate % with respect to Malaysia only or does it change also.
Cheers
CheenuSing
Hi @CheenuSing,
The PBIx file is here : https://mega.nz/#!HApW0KYS!-OqOsLaRfT_rlgKUhTjq6gJvG0v3u4k02f8hP_UcLT8
1. The first three columns are the raw data
2. The rest of the columns are the expected ouput
3. I am interested in one column only, the proportion/percentage of the selected Type over a selected Level, e.g. 'Level 1', or 'Level 2'. Column F if type = Malaysia and Level = Level 1, Column I if type = Malaysia and Level = Level 2
4. Yes, I would like the user to be able to select a different Type, e.g. 'India','China', and also select the level of hierarchy, e.g. 'Level 1','Level 2'
hi @Anonymous
Please upload to Google or One Drive and share the link. The link you provided takes me to a suspicious site.
Cheers
CheenuSing
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |