Calculate totals for the values containing the same ItemID number

[Anonymous]

Hello,

I have a table containing four columns as you can see on the attached image below. The column called ItemID can contain the same ItemID multiple times and different BOMConsump number to each row. So, what I did was to create a column called Measure to take the total BOMConsump amount for each ItemID, but as you can see the formula that I have does not give the correct outcome since as for example the total for ItemID 10013 should be 4.020 and total for ItemID 10273 should be 45.030,80.

The formula that I have for measure is:

Measure = CALCULATE(SUM(PRODJOURNALBOM[BOMCONSUMP]);Filter(All('PRODJOURNALBOM');'PRODJOURNALBOM'[ITEMID]=Max('PRODJOURNALBOM'[ITEMID])))

I've also tried to use this formula here:

Table = SUMMARIZE(PRODJOURNALBOM;PRODJOURNALBOM[ITEMID];"ID";SUM(PRODJOURNALBOM[BOMCONSUMP]))

I'm using the Direct Query.

Do you have any idea of how I can fix it?

Best regards,
Ina 

[v-diye-msft]

Hi @Anonymous 

Try below:

Measure = CALCULATE(SUM(PRODJOURNALBOM[BOMCONSUMP]);AllEXCEPT('PRODJOURNALBOM');'PRODJOURNALBOM'[ITEMID]))

Or

Measure = CALCULATE(SUM(PRODJOURNALBOM[BOMCONSUMP]);All('PRODJOURNALBOM'),VALUES('PRODJOURNALBOM'[ITEMID]))

[Anonymous]

Hi @v-diye-msft ,

The formulas you suggested give me the same total result as if I use this formula that I did use before:

Measure = Calculate(SUM(PRODJOURNALBOM[BOMCONSUMP]);Filter(All('PRODJOURNALBOM');'PRODJOURNALBOM'[ITEMID]=Max('PRODJOURNALBOM'[ITEMID])))

And as you saw above the result was not right. I don't get how the formula gets this result... If you look at the picture below, you see that the Measure for ItemID 10001 should be 0, the Measure for ItemID 10013 should be 24664 and the Measure for ItemID 10024 should be 180 and this is because here there is only one row for each ItemID but ofcourse if there are more the Measure should calculate the total.

Best regards,
Ina

[v-diye-msft]

Hi @Anonymous 

you might consider creating pbix file that will contain some sample data (remove the confidential info), upload the pbix to onedrive or dropbox and share the link to the file. I'd like to check it further.

[Anonymous · Accepted solution]

Hello @v-diye-msft,

Sorry for the late reply! But I think I've figured it out. 
I have some filters on the page but since I am using a formula containing "all" it seems to ignore the filters... so I think I need to add maybe allexcept into the formula or at least find some way to "add" the filters into the formula!

Best regards,
Ina

[amitchandak]

@Anonymous , try allexcept ,

This one is good example how to use :https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390

[Anonymous]

I am not an expert on this, but I think that after using ALL, you lose the previous row context and MAX(ItemID) is not evaluated in the right context. Try defining that in a variable:

Measure =
VAR varItemID = 'PRODJOURNALBOM'[ITEMID]

RETURN
CALCULATE(
    SUM( PRODJOURNALBOM[BOMCONSUMP] ),

    FILTER( ALL( PRODJOURNALBOM ); PRODJOURNALBOM[ITEMID] = varItemID )

)

This way the variable is evaluated before transitioning row context. I guess this is what you are trying to achieve.

Regards