Calculate sum for selected productnumbers and rest productnumbers

[Towerstar]

Hello,

I am looking for a formula to display in a stacked column chart my focus products and rest products.

Lets say I have 10 products. My focus  products are 1, 4, 7 and 9. My target is to get a formula where I see in the legend 1,4,7,9, rest. (and rest includes 2,3,5,6,8,10). 

I started like this.

Product Quantity 1, 4, 7, 9 =

CALCULATE(SUM('Rohdaten'[Quantity]), 'Rohdaten'[ProductID]="1") +

CALCULATE(SUM('Rohdaten'[Quantity]), 'Rohdaten'[ProductID]="4") +

CALCULATE(SUM('Rohdaten'[Quantity]), 'Rohdaten'[ProductID]="7") +

CALCULATE(SUM('Rohdaten'[Quantity]), 'Rohdaten'[ProductID]="9")

But now I dont know how i can caluclate the sum of the rest products and the formula is quite long. 

Do you have any tip how I can solve this problem in the best way? 

Thank you in advance. Your help is appreciated.

Best 

towerstar

[v-jianboli-msft · Accepted solution]

Hi @Towerstar ,

Based on your description, I have created a simple sample:

You can create a slicer to select the products that need to be summed:

Then create a measure to calculate the rest:

Rest = CALCULATE(SUM(Rohdaten[Quantity]),ALL(Rohdaten))-SUM([Quantity])

Add it to the chart:

Open the data labels:

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Towerstar]

Hi Syk, 

thanks for your reply. It is just an example. In real its more than 100 products... I need the total quantity in my chart stacked by the focus products and rest products. How can I solve this problem with or in my formula? 

[Syk]

I would create a new column to indicate if a product is a focus product or not and base my measure off the new field. Or you can group the IDs into focus and rest groups but it really depends on what you can use to categorize the products (hopefully thats not a manual process?)

[Syk]

If you want the measure to be hardcoded specifically for those ids, one simpler way would be to use OR in your calculate expression

Product Quantity 1, 4, 7, 9 =
CALCULATE (
    SUM ( 'Rohdaten'[Quantity] ),
    'Rohdaten'[ProductID] = "1"
        || 'Rohdaten'[ProductID] = "4"
        || 'Rohdaten'[ProductID] = "7"
        || 'Rohdaten'[ProductID] = "9"
)

If you want to calculate that quantity on the fly, I'd suggest using a slicer on the report page where users can select which ID they'd like to see instead of creating a measure.