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 Powerbi users,
Greetings !!!
I am faced with a unique challenge and not sure if I can find a solution in PowerBI desktop. I have the following table in excel:
Actual Data | |||
Audit compliances | May-19 | Jun-19 | Jul-19 |
No. of CC Audits | 139 | 177 | 126 |
1. No Transactions | 11 | 25 | 4 |
2. 0% Compliant | 34 | 56 | 17 |
3. > 50% Compliant | 19 | 21 | 16 |
4. 50 <> 99% Compliant | 57 | 43 | 64 |
5. 100% Compliant | 18 | 32 | 25 |
% of Grand Total | 139 | 177 | 126 |
Now, I need to keep the first row static i.e. to show it as numbers, thereafter from 2nd row onwards, i need to have the number converted to % of grand total as below:
Data expected from PowerBI | |||
Audit compliances | May-19 | Jun-19 | Jul-19 |
No. of CC Audits | 139 | 177 | 126 |
1. No Transactions | 8% | 14% | 3% |
2. 0% Compliant | 24% | 32% | 13% |
3. > 50% Compliant | 14% | 12% | 13% |
4. 50 <> 99% Compliant | 41% | 24% | 51% |
5. 100% Compliant | 13% | 18% | 20% |
% of Grand Total | 100% | 100% | 100% |
The result that i am getting from doing % of power bi is as under:
It would be great if anyone can share a solution with the query to the above.
Regards,
Gourang shah
Solved! Go to Solution.
You may refer to the measure below.
Measure =
SWITCH (
SELECTEDVALUE ( 'Table'[Audit compliances] ),
BLANK (), "100%",
"No. of CC Audits", [Count],
FORMAT (
DIVIDE (
[Count],
CALCULATE (
[Count],
'Table'[Audit compliances] = "No. of CC Audits"
)
),
"0%"
)
)
You may refer to the measure below.
Measure =
SWITCH (
SELECTEDVALUE ( 'Table'[Audit compliances] ),
BLANK (), "100%",
"No. of CC Audits", [Count],
FORMAT (
DIVIDE (
[Count],
CALCULATE (
[Count],
'Table'[Audit compliances] = "No. of CC Audits"
)
),
"0%"
)
)
You need to create a sort sequence for column Audit compliances and mark that as sort column for this column and then sort on Audit compliances. It will do it.
https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column
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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |