calculate the percentage of each Category

[dofrancis3]

Dear Colleagues,

Please, I would like your support!

As you can see, I have two tables (Table_1 and Table_2) so, I'd like to calculate the percentage of each Category (table_1) according to each status (Table_2).

please see: https://docs.google.com/spreadsheets/d/1u-eWhVPJhTvuFKx9DO9eWt65w7wSnn_o4twDuHRyPrg/edit?usp=drive_l... 

Bellow are the formula:

P1= ZD DIVIDE P1 (12-23 months) + P1 (24-59 months)

P3= Uim DIVIDE P3 (12-23 months) + P3 (24-59 months)

MC1= ZD DIVIDE MC1 (12-23 months) + MC1 (24-59 months)

MC2= Uim DIVIDE MC2 (12-23 months) + MC2 (24-59 months)

[v-yajiewan-msft]

Hi @dofrancis3 ,

Since I’m unable to open the link file you shared, I will infer the data structure based on your description and provide the solution. If this is not feasible for you, please feel free to reply!

Table_1:

Table_2:

Then follow these steps to create the necessary measures:

Total_Category = 
SUM(Table_1[12-23 months]) + SUM(Table_1[24-59 months])

ZD_Percentage = 
VAR TotalValue = 
    CALCULATE(Table_1[Total_Category], ALLEXCEPT(Table_1, Table_1[Category]))
RETURN 
DIVIDE(
    SUM(Table_2[ZD]),
    TotalValue,
    0
)

Uim_Percentage = 
VAR TotalValue = 
    CALCULATE(Table_1[Total_Category], ALLEXCEPT(Table_1, Table_1[Category]))
RETURN
DIVIDE(
    SUM(Table_2[Uim]),
    TotalValue,
    0
)

Result for your reference:

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dofrancis3]

Dear @v-yajiewan-msft  thank you for your replay but the structures of the table that i have attached isn't the same 

[dofrancis3]

This the structure of table_1

[v-yajiewan-msft · Accepted solution]

Hi @dofrancis3 , thank you for your feedback.

Check the following measures:

P1_Percentage = 
DIVIDE(
    SUM(Table_2[ZD]),
    SUM(Table_1[P1 (12-23 months)]) + SUM(Table_1[P1 (24-59 months)]),
    0
)

P3_Percentage = 
DIVIDE(
    SUM(Table_2[Uim]),
    SUM(Table_1[P3 (12-23 months)]) + SUM(Table_1[P3 (24-59 months)]),
    0
)

MC1_Percentage = 
DIVIDE(
    SUM(Table_2[ZD]),
    SUM(Table_1[MC1 (12-23 months)]) + SUM(Table_1[MC1 (24-59 months)]),
    0
)

MC2_Percentage = 
DIVIDE(
    SUM(Table_2[Uim]),
    SUM(Table_1[MC2 (12-23 months)]) + SUM(Table_1[MC2 (24-59 months)]),
    0
)

Result:

Best regards,

Joyce

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dofrancis3]

Dear @v-yajiewan-msft Thank you