DAX help

[Dileep316]

Hi,

I am trying to create a measure that calculates out of stock at a combined warehouse level rather than an individual warehouse level. 

Please see attached Excel file with the source data for the PowerBI sample file. In the Excel file, I have outlined the calculation required to get the count of out of stock. As per the Excel calculation, count of out of stock product is 14.

In the PowerBI sample file, I've tried a couple of calculations but it doesn't give me the desired output. What am I doing wrong with the DAX/measures? 

EDIT - linke to sample files here

https://www.dropbox.com/sh/bs1hung04v2sgd0/AACuECDr3jTB2w_IQXJKtRboa?dl=0

Thanks,

Dileep

[v-yangliu-msft]

Hi  @Dileep316 ,

Here are the steps you can follow：

1. Create measure.

Out of stock flag =
var _SumofStockonhand=CALCULATE(SUM('Table1'[Stock on hand]),FILTER(ALL('Table1'),'Table1'[Item]=MAX('Table1'[Item])))
var _SumofUnshipped=CALCULATE(SUM('Table1'[Unshipped]),FILTER(ALL('Table1'),'Table1'[Item]=MAX('Table1'[Item])))
return
IF(_SumofStockonhand -_SumofUnshipped <0,"Y","N")

Out of stock count =
CALCULATE(DISTINCTCOUNT(Table1[Item]),FILTER(ALL(Table1),[Out of stock flag]="Y"))

2. Result:

Best Regards,

Liu Yang

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

[Dileep316]

Thanks Liu for the reply. 

I should have mentioned that there are other filters/parameters I need to apply which I excluded from the original sample file to keep it simple. 

For example: each Item has a product manager. So, I need to be able to view the out of stock count by each manager. Using the solution provided by you, it shows the same out of stock count for all product managers.

There are also other filters that I need to be able to apply, like, if product is from certain supplier - exclude it or if product is in a certain category - exclude it etc.
I think, from the solution you have provided, if it shows out of stock count as 1 where out of stock flag = Y and if it shows as 0 where out of stock flag = "N", then it might work with the filters I need to apply. 

Thanks for looking into this,

Dileep

[v-yangliu-msft]

Hi  @Dileep316 ,

Here are the steps you can follow：

1. Create calculated table.

Table2 =
SUMMARIZE(
    'Table1','Table1'[Item],"Sum of Stock on hand",SUM(Table1[Stock on hand]),"Sum of Unshipped",SUM('Table1'[Unshipped]))

2. Create calculated column.

Out of stock flag = IF('Table2'[Sum of Stock on hand] - 'Table2'[Sum of Unshipped] <0,"Y","N")

Out of stock count =
COUNTX(FILTER(Table2,Table2[Out of stock flag]="Y"),Table2[Item])

3. Result:

Best Regards,

Liu Yang

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

[Dileep316]

Thanks Liu for the response. 

The original table I'm using has around 25 columns and 3 million rows. I have done something similar to Summarize by creating another table and using the group by function. But I feel it's a duplication of the original table and increases the processing time/file size. 

Are there any DAX functions I can use in the measure to achieve the required result without having to create another table?

I agree yours is a possible solution but will keep this thread open incase there is another option.

Thanks,

Dileep

[amitchandak]

@Dileep316 , I do not see any attachments. Please share the link again

[Dileep316]

Please see the link for the files. Hopefully you can access them.

https://www.dropbox.com/sh/bs1hung04v2sgd0/AACuECDr3jTB2w_IQXJKtRboa?dl=0