cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

Problems calculating percentage over subtotal by option.

Good morning

Given the following example table:

Date Town Town Km_Asfaltados

1-2-2022 Madrid Getafe 10

3-2-2022 Seville Two Sisters 5

4-7-2022 Madrid Las Rozas 7

4-7-2022 Madrid Getafe 5

4-7-2022 Madrid Alcorcón 3

How can I calculate using DAX, the percentage of each town in asphalted km with respect to the total of km belonging to the same city of its corresponding day and show it in a new column?, I have tried to generate a summary table with "SUMARIZE" obtaining the subtotals by city, then creating a relationship between both tables by the city field to later use the function "DIVIDE" between the total of asphalted kg of each table to obtain the percentage, but I can not perform the formula well, plus I have the feeling that I am killing flies with cannon shots because I think it should be possible to do without generating a new summary table.

Please if you are so kind to throw me a cable to illuminate me, thank you very much!!

Greetings,

Jose.

1 ACCEPTED SOLUTION
Syndicate_Admin
Administrator
Administrator

Well indeed, it was complicating my life greatly. (It's what it takes to be a newbie at this xD)

As I have already solved it, I detail here the solution in case someone in the future could serve for a similar problem.

1.- I create a new measure that will house the totalization of the field Km_Asfaltados

ZMED_TotalKgAsfaltados = sum(Carreteras[Km_Asfaltados])

2.- I create a new calculated column that will obtain the total for each town.

ZCOL_Total_por_Pueblo           = 
 CALCULATE(SUM(Carreteras[Km_Asfaltados]),ALLEXCEPT(Carreteras,Carreteras[Pueblo],Carreteras[Fecha]))

3.- I calculate the percentage that represents the asphalted km of that town with respect to the total of its province.

ZMED_%_S/Pueblo = 
DIVIDE([ZMED_TotalKmAsfaltados], [ZCOL_Total_por_Pueblo])

Thanks a lot

Greetings!!.

View solution in original post

1 REPLY 1
Syndicate_Admin
Administrator
Administrator

Well indeed, it was complicating my life greatly. (It's what it takes to be a newbie at this xD)

As I have already solved it, I detail here the solution in case someone in the future could serve for a similar problem.

1.- I create a new measure that will house the totalization of the field Km_Asfaltados

ZMED_TotalKgAsfaltados = sum(Carreteras[Km_Asfaltados])

2.- I create a new calculated column that will obtain the total for each town.

ZCOL_Total_por_Pueblo           = 
 CALCULATE(SUM(Carreteras[Km_Asfaltados]),ALLEXCEPT(Carreteras,Carreteras[Pueblo],Carreteras[Fecha]))

3.- I calculate the percentage that represents the asphalted km of that town with respect to the total of its province.

ZMED_%_S/Pueblo = 
DIVIDE([ZMED_TotalKmAsfaltados], [ZCOL_Total_por_Pueblo])

Thanks a lot

Greetings!!.

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.