Count No. of days below target based on the selection of slicer

[tiran]

I have data set as table below; everyday there will be a data of sales volume from each country and each product and I need to count no. of days that sales are below these daily targets.

So I did create the new column with

No. of days below target = if((Table1[Sales])<(Table1[Daily Target), 1,0)

But I also have 2 slicer boxes; Country and Product. From table below, it shows 1 for product C in China on 3 Jan 19 which is correct.

Now when I use the slicer to choose only China with all products selected, it is still showing one on 3 jan 19 while in fact it should be zero since sum of all China's product sales is above sum of all China's daily target.

Any suggestion how to achieve this? Basically the No. of days below target must be calculated from sum of sales versus sum of daily target from the selection of slicer which could be any combination e.g.  all products from all countries, all USA+all China, Product B USA + Product A Singapore, Product C from all countries etc.

Thanks in advance!

[Anonymous]

DAYSDOWN =

//Looking for a simple count of days in the current month where the plant didn't operate indicated with a 0 in the "pounds" column.   Later evaluate MOM, YTD, etc.

CALCULATE (

COUNT ( 'CALENDAR'[Date] ),

SUMMARIZE (

'Inventory & Production',

'Inventory & Production'[POUNDS],

'CALENDAR'[Date],

"DOWNTIME DAYS", SUM ( 'Inventory & Production'[POUNDS] )),

[DOWNTIME DAYS] = 0

)

)

[v-lili6-msft]

hi, @tiran 

First, you should know that calculated column and calculate table can't be affected by any slicer. you could create a measure instead of column.

Notice:

1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.

here is reference:

https://community.powerbi.com/t5/Desktop/Different-between-calculated-column-and-measure-Using-SUM/t...

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Second, you could create a measure like this:

Measure 3 = IF(CALCULATE(SUM(Table1[Sales]))<CALCULATE(SUM(Table1[Daily Target])),1,0)

Result:

Best Regards,
Lin

[tiran]

Hi @v-lili6-msft ,

Thank you for your help. Is there any way I can get the 'Total' for Measure 3 displayed correctly? The measure works well with slicer but I still cant get total no. of days below target.

[v-lili6-msft · Accepted solution]

hi, @tiran 

This is a measure totals problem. Very common.

See this post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

You could try to add a measure like this:

Measure = var _table=SUMMARIZE(Table1,Table1[Date],"a",[Measure 3]) return
SUMX(_table,[a])

Best Regards,

Lin

[tiran]

hi @v-lili6-msft ,

Thanks again that measure is perfect!