Measure SubTotals and the Ceiling function

[shaunwilks]

I cannot work out how to get this going. I have a situation where I buy from a supplier in specific qtys.

The same item from dfferent countries may have different buy qtys.

So if the amount each country needs is not a multiple of the buy qty then the buy qty must be rounded up to that multiple.

Ie I need to buy 35, they only sell in lots of 10, so I need to buy 40.

The CEILING function does this very well at the line level.

But I am having tremendous troubles when trying to Sum a measure using this value in a matrix visual.

Here is my table...

Item     Country        BuyUnits       Required

Apple   US                50                  210

Apple   AUS              50                  330

Apple   UK                25                  405

Here is my measure

Buy = 
if(ISFILTERED(Table1[Country]),
 CEILING(Sum(Table1[Required]),Sum(Table1[BuyUnits])),
ceiling(Sumx(Table1,Table1[Required]),sumx(Table1,Table1[BuyUnits])))

When its in a matrix the "Buy" measure works well and shows 

US  250

AUS  350 

UK    425

This is correct

But when I show at the Item level

Apple = 1000

The total should be 1025

Now in the past ove used the is filtered to change the formula for the line level vs the subtotal level.

I have tried it here but not really sure what the formula should be for the second part of the is filtered command.

Ive used ceiling again but it hasnt worked.

All I want to do is sum the results of my calculation.

Thoughts?

[v-cherch-msft · Accepted solution]

Hi @shaunwilks 

You may try to create two measures like below:

Measure = CEILING(Sum(Table1[Required]),Sum(Table1[BuyUnits]))

Buy =
IF ( ISFILTERED ( Table1[Country] ), [Measure], SUMX ( Table1, [Measure] ) )

Regards,

Cherie