Calculate percentage of hours of each people and it must be 100%

[Anonymous]

Good morning,

I need help, because I can´t get that this dashboard works. I have a lot of people, and I need to calculate the percentage of hours from each of the total hours from each. Also, I need the result without decimals, because I need in text format.

The problem is, when the result appears apparently is OK, when I download the report, el value has a lot of decimals, and when I round this values, the total % form each is up or down of 100%. The minimal value must be 1 and the maximum value must be 100.

And the most frustrating is that the total value in the dashboard appears 100% but if you add all values the result isn´t 100%. The total value must be the number of people I have multiplied by 100, because form each will have 100%.

For example:

The DAX formula is the next: 

% Oct 17 = 100 * DIVIDE(SUM('Informe'[Oct 17]);CALCULATE(sum('Informe'[Oct 17]);ALL('Informe'[Profesional]);ALL('Informe'[Contraseña])))

Profesional	Contraseña	Oct 17	Nov 17	Dic 17
00001	Contra_1	63986388888888800,00	63986388888888800,00	63986388888888800,00
00001	Contra_2	452,00	452,00	452,00
00001	Contra_3	19999972222222200,00	0,00	0,00
00001	Contra_4	10999872222222200,00	10999872222222200,00	10999872222222200,00
00001	Contra_5	10597944444444400,00	0,00	0,00
00001	Contra_6	130,00	130,00	0,00
00001	Contra_7	985,00	985,00	0,00
00001	Contra_8	14285714285714200,00	0,00	0,00
00001	Contra_9	310,00	310,00	0,00
00001	Contra_10	18224522222222200,00	18224522222222200,00	0,00
00001	Contra_11	56405714285714200,00	0,00	0,00
00001	Contra_12	68571428571428500,00	0,00	0,00
00001	Contra_13	5299997222222220,00	50476164021164000,00	0,00
00001	Contra_14	19559997222222200,00	19559997222222200,00	19559997222222200,00
00001	Contra_15	620,00	29523809523809500,00	0,00
00001	Contra_16	11899997222222200,00	11899997222222200,00	11899997222222200,00
00001	Contra_17	166,00	166,00	166,00
00001	Contra_18	174,00	174,00	174,00
00001	Contra_19	60,00	60,00	60,00
00001	Contra_20	60,00	60,00	60,00
00001	Contra_21	22799972222222200,00	22799972222222200,00	22799972222222200,00
00001	Contra_22	308,00	308,00	0,00
00001	Contra_23	193,00	193,00	193,00
00001	Contra_24	6612,00	6612,00	6612,00
00001	Contra_25	8999997222222220,00	8999997222222220,00	8999997222222220,00

Thanks for the help,

Mayte

[v-qiuyu-msft]

Hi @Anonymous,

What exactly do you mean? When I use your sample data and DAX to create a measure, the total value is 100, all % Oct 17 values are totaled to 100 as well. Would you please share some screenshots to clarify where the issue is? 

Best Regards,

Qiuyun Yu 

[Anonymous]

Hello @v-qiuyu-msft,

The problem is that the total value is incorrect, because when you add every value with a calculator, the total value is not correct. And when you download the report in excel, and see the values in a dinamic table, the total value of each employee, in some cases, are up or down to 100%.

Best Regards,

Mayte

[v-qiuyu-msft]

Hi @Anonymous,

It seems that you calculate total from all % Oct 17 row values and yo don't get the exact 100 result, right? 

The issue should be each % Oct 17 row value is rounded, eg: 1.234 rounded in 2 decimal place then becomes 1.23 which is less than actual one. 

Best Regards,
Qiuyun Yu 

[Anonymous]

hi @v-qiuyu-msft,

It is exacly that you are talking, the problem is that I don´t get the exact 100 result, and I would need or the 100% exactly result or get a function which read the result and add or subtract the values and modifify them to get the 100% exactly result.

I was thinking to get this using macro in excel, but I think that I would mix two things (macros/Power BI) and there must be something that I can do in power bi to get it.

Best regards, and thank you very much for your interest and your time,

Mayte