topic Re: calculate the percentage of each Category in DAX Commands and Tips

calculate the percentage of each Category

dofrancis3 — Sun, 09 Feb 2025 22:52:51 GMT

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_link

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)

Re: calculate the percentage of each Category

Anonymous — Mon, 10 Feb 2025 07:02:42 GMT

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.

Re: calculate the percentage of each Category

dofrancis3 — Mon, 10 Feb 2025 07:32:12 GMT

Dear Anonymous thank you for your replay but the structures of the table that i have attached isn't the same

Re: calculate the percentage of each Category

dofrancis3 — Mon, 10 Feb 2025 07:55:05 GMT

This the structure of table_1

Re: calculate the percentage of each Category

Anonymous — Tue, 11 Feb 2025 07:45:12 GMT

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.

Re: calculate the percentage of each Category

dofrancis3 — Tue, 11 Feb 2025 09:52:34 GMT

Dear Anonymous Thank you