Correct Percentage at each level

[takui12]

Hi guys...I know it should be simple, and I've tried all day all the hints around but I wasn't able to make it works...

Basically, I've  a measure that calculate the % of 2 fields. Problem is, at row level it works fine, at group level, instead of just sum the values inside its rows, it makes the calculation again, resulting in a wrong number..to fix it, I made an intermediate step which works normally but, for some unknown reason, isn't working for the actual

see pics for example...instead of  2593K, I should see the sum of 1851K

Actual-->Actual_PF-->Actual_PF_Final is wrong
Forecast --> Forecast_PF-->Forecast_PF_Final works

Thank

Edit 19/02: Hi, I've update my post with more clear info because meanwhile I've made 1 step forward. As you can see from the picture above, I've 6 columns: first 3 are from the database, second 3 are from an excel. Actual and Forecast are the starting data, no manipulations. They are multiplied by the same % in the Actual_PF and Forecast_PF columns. My problem which I'm trying to solve is to have the sum of these results, correct for their category (bold lines). As you may notice, in the last 2 columns (Actual_PF_Final and Forecast_PF_Final), the sum at above levels works for the forecast but not for the actual

I'm using this approach:

intermediate step is generated by:

ImponibileIncasso x Forecast% = MAX('Estrazione_F25(18Feb - %PF)'[Forecast%]) * SUM('BI FactIncassato25'[ImponibileIncasso])

where 'Estrazione_F25(18Feb - %PF)'[Forecast%]) is just a column with % for each lines.

while Actual_PF_Final is:

A_PF =

VAR __table = SUMMARIZE('BI FactIncassato25','BI FactIncassato25'[Utilizzatori],"__value",[ImponibileIncasso x Forecast%])

RETURN

IF(HASONEVALUE('BI FactIncassato25'[Utilizzatori]),[ImponibileIncasso x Forecast%],SUMX(__table,[__value]))

in the same way, Forecast intermediate is generated by:

Forecast%PF x Forecast =

MAX('Estrazione_F25(18Feb - %PF)'[Forecast%]) * SUM('Estrazione_F25(18Feb)'[Forecast])

and Forecast_PF_Final is:

FC_PF =

VAR __table = SUMMARIZE('Estrazione_F25(18Feb)','Estrazione_F25(18Feb)'[Utilizzatori],"__value",[Forecast%PF x Forecast])

RETURN

IF(HASONEVALUE('Estrazione_F25(18Feb)'[Utilizzatori]),[Forecast%PF x Forecast],SUMX(__table,[__value]))

it's totally the same approach, and they multiply the same % Column. I'm just changing the numerator but, Idk why, even if both Intermediate steps shown equivalent numbers, the final step of the actual, only shows values at line level but not at aggregated (bold) level, while forecast shows also them. Someone knows why and how I could try to fix it? Thanks guys

SuperCategory	Categoria	Utilizzatori	Budget	QuotaPBudget	QuotaABudget	QuotaP%	QuotaA%	Forecast	Forecast x Quota P%
Fruit	Apple	A	28571,43	20.000,00 €	8.571,43 €	70,00%	30,00%	2.470.585,00 €	1.729.409,50 €
Fruit	Apple	B	0	0,00 €	0,00 €			0,00 €
Fruit	Apple	C	0	0,00 €	0,00 €			0,00 €
Fruit	Apple	D	474000	474.000,00 €	0,00 €	100,00%	0,00%	0,00 €	0,00 €
Fruit	Apple	E	65000	65.000,00 €	0,00 €	100,00%	0,00%	0,00 €	0,00 €
Fruit	Apple	F	2000	2.000,00 €	0,00 €	100,00%	0,00%	0,00 €	0,00 €
Fruit	Apple	G	30000	30.000,00 €	0,00 €	100,00%	0,00%	122.527,89 €	122.527,89 €

[speedramps]

We want to help you but your description is too vague. Please write it again clearly.

I suspect you need to use SUMX

learn how here

• https://learn.microsoft.com/en-us/dax/sumx-function-dax
• https://www.youtube.com/watch?v=pxcBfC0CHoU
• https://www.youtube.com/watch?v=2P0-6wlGQJo

Please click thumbs up for the suggestion.

Then click accept solution if it works.

You can click accept multiple solutions.

Thanks

[takui12]

I've add new information in the first post, thank you!

[takui12]

Hello, thanks for the reply, I've already tried sumx but didn't work.  If you check my pic, you could notice that the 70% (2nd column) of the 1st column is equal to the value in the 3rd: 70% * 2.470 =  1.729
But the value in bold should be also equal to 1.729+ 122, which isn't. Because the above level is still calculating the % * value instead of doing the sum of its part, I can't use the 2nd column but I need the 3rd.

. is more clear now? 2.556 = 2.593*98,57% which is wrong cos the correct result there should be 1.729 + 122 = 1.851, which is showing correctly for the forecast but not for the actual even if the formulas are the same...