Problems calculating percentage over subtotal by option.

[Syndicate_Admin]

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.

[Syndicate_Admin · Accepted solution]

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!!.