Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Switch function not calculating total

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?
Need Help

8 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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.

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        7 years ago

        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. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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.