How to Calculate Percentage of 2 columns from Matrix Table

[0Experience]

Hello everyone,

I hope this message finds you well. I am relatively new to Power BI, and I have a question that may seem quite basic, for which I apologize in advance.

In my original data table, I have several columns, and in one particular column (Statistics), there are multiple values such as "Actual," "Total Cleared," etc.

I am currently working on a Table Matrix, where I have placed the Year in the rows and Statistics in the columns. The values for the Statistics are sourced from the Value column in my dataset. After setting this up, the table matrix looks like the following:

Now, my goal is to calculate the percentage of "Actual" and "Total Cleared" from the matrix table (Total/Actual). After conducting some research and web surfing, it seems I might need to use DAX to create a new column with the percentage.

However, I'm facing a challenge since "Actual" and "Total Cleared" are not direct columns in my Data table; they are part of the Statistics column.

Dummy data added with the post

REF_DATE	Statistics	VALUE
2018	Actual incidents	54492
2018	Cleared otherwise	8233
2018	Total cleared	24674
2019	Actual incidents	3
2019	Actual incidents	3052
2019	Cleared otherwise	524
2019	Total cleared	1404
2019	Actual incidents	17
2019	Actual incidents	8108
2019	Cleared otherwise	850
2019	Total cleared	1754
2019	Actual incidents	2608
2019	Total cleared	169

Expected Output

Year	Actual	Cleared	% Percentage
2018	1195	563	0.47113
2019	1236	1002	0.81068

Any help or suggestions would be highly appreciated. Thank you in advance for your assistance!

[Daniel29195]

@0Experience 

what you can do is used power query to pivot your table like this : 

steps : 

1.select statistics columm 

2. go to transform --> pivot  

3. set the below configuration

4.  you will ahve the following strucutre:

now you can use measure : sum( actual_column) / sum(total_cleared) 

If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. Dont forget to hit that  thumbs up button 🫡👍

[0Experience]

@Daniel29195, thanks for your answer, and it should work. However, in here, I used dummy data (a very small portion of my main dataset). My main dataset has many columns, and in the Statistics column, I have numerous items. So if I transpose the Statistics column for the table, it will mess up the whole table and other visualizations I already have in the dashboard.

Without transposing the entire table, is there any other option, please?

Thank you.

[Daniel29195 · Accepted solution]

@0Experience 

i totally got you . 

result : 

actual =

CALCULATE(

    SUM(table11[VALUE]),

    table11[Statistics] = "Actual Incidents"

)

Total =

CALCULATE(

    SUM(table11[VALUE]),

    table11[Statistics] = "Total cleared"

)

% =

DIVIDE(

    [Total],

    [actual]

    ,0

)

final result 

let me know if this helps 

If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. Dont forget to hit that  thumbs up button 🫡👍

[0Experience]

Thanks @Daniel29195 

[Ritaf1983]

Hi @0Experience 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[0Experience]

@Ritaf1983 thanks for your reply. I added some summy data, and expected output format.

Thanks again