Request to assit with static first row as numbers and remaining rows to be % of grand total

[gourangshah24]

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

[v-chuncz-msft · Accepted solution]

@gourangshah24 

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%"
    )
)

[amitchandak]

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