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 look...
Need Help
  • 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. 

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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.

         

  • 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.