Switch function not calculating total

[Anonymous]

Not sure if this is a duplicate post.  Submitted before but received an error and I don't see the post anywhere.

Anyway, I have the following SWITCH funtion which gives me the exact result I am looking for:

Daily Non-Compliant = SWITCH(

TRUE(),

'Prod Ops'[Prod Shipments]=0 && [Test Shipments] = 0,ROUNDUP(sum('Ops Plan'[Weekly Planned Frequency])/265,0),

0

)

However, as you can see from the attached picture, the total for this column displays zero.  Anyone know why?

[Greg_Deckler · Accepted solution]

This looks like a measure totals problem. Very common. See my 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

[Anonymous]

Thank you, @Greg_Deckler.  The Final Word solution worked.  

I created this measure:

Daily Non-Compliant 2 = SWITCH(
TRUE(),
[Total Shipments]=0, ROUNDUP(sum('Ops Plan'[Weekly Planned Frequency])/265,0),
0
)

Then I created this measure:

Daily Non-Compliant = 
VAR __table = SUMMARIZE('Ops Plan','Ops Plan'[Carrier SCAC] ,"__value",[Daily Non-Compliant 2])
RETURNIF(HASONEVALUE('Ops Plan'[Carrier SCAC]),[Daily Non-Compliant 2],SUMX(__table,[__value]))

The totals now seem to be correct.  

[Anonymous]

I will delve into this and see if I can come up with a formula that works based on your post.  Any idea why it is showing zero for this particular column?  I double checked the totals for my other measures and they seem to be correct.

[v-jiascu-msft]

Hi @Anonymous,

Did you solve it? It could be like this. 

Daily Non-Compliant =
SWITCH (
    TRUE (),
    'Prod Ops'[Prod Shipments] = 0
        && [Test Shipments] = 0, ROUNDUP ( SUM ( 'Ops Plan'[Weekly Planned Frequency] ) / 265, 0 ),
    NOT HASONEVALUE ( 'Prod Ops'[Prod Shipments] )
        && NOT HASONEVALUE ( [Test Shipments] ), SUM ( 'Ops Plan'[Weekly Planned Frequency] ),
    0
)

Best Regards,
Dale

[Anonymous]

@v-jiascu-msft, I'm not sure exactly how the formula is supposed to work, but you can see below that there are errors in the format.

[v-jiascu-msft]

Hi @Anonymous,

It seems the 'Prod Ops'[Prod Shipments] is a measure. In short, the newly added step judges the context of the Total and then assign the proper value to the Total before it takes the default value 0. 

What should the 'Prod Ops'[Prod Shipments] be for the Total? Then it could be like below.

'Prod Ops'[Prod Shipments] <> 0, SUM ( 'Ops Plan'[Weekly Planned Frequency] ),

Best Regards,
Dale

[Anonymous]

@v-jiascu-msft, I may have to try a different approach.  I replaced the measure in the formula you provided with the column I used to get the measure.  When I did this, a total was displayed, however it was the total of the entire Weekly Planned Frequency column.  What I am trying to do is this:

There is a weekly plan with the expected number of shipments.

You are either compliant or non-compliant.

If you have Production Shipments or Test Shipments, you are compliant.  

If you have zero test shipments and zero production, you are non-compliant.

If you are non-compliant, the number of non-compliant shipments is equal to the number we expected (the Weekly Plan)

So, the total in the non-compliant column should only be those carriers with non-compliant shipments.  With the formula you provided, the total seems to be showing the sum of the entire Weekly Planned Frequency column.

[Greg_Deckler]

The total line evaluates in the context of ALL rows in the table. So, it is likely that one of your criteria for the SWITCH to not return 0 is not true in the context of ALL and thus you get 0 as specified in the formula.