Forum Discussion

Russell93's avatar
Russell93
Frequent Visitor
3 years ago
Solved

Conditional Measure Not Working the way I Imagined - any help greatly appreciated

So I have built a Sales Effciency data model with visuals showing effciency ratios each month of the year. 

 

I had a request to show which stores have shown effciency improvements month-over-month, and which stores have declined in effciency month-over-month. I would like to return a binary conditional where 0 indicates a gain and 1 indicates a decline, to be used for Green and Red color coding respectively on a map visual. 

 

Before I get the Map visual going, I wanted to test my measure in a table view, here is a quick screenshot:

The first three measures in the example table work correctly - I'll paste how to measures are defined below just for reference - but as you can see my Eff MoM Conditional is returnign zeros for all stores but I would like for any stores with a negative MoM Efficiency % Chg to return a 1.

 

My conditional measure is just a simple If Statement, so maybe there is another way I could build that measure to return the results I was expecting. I have played around with this a good deal and would appreciate any insights you may have. Thanks for your time. 

 

  • probably it is returning value as a whole number, not in decimal. try dividing with 100. see if that works.

     

    Eff MoM Conditional =
    IF (
    [Efficiency % Chg] / 100 < 0,
    1,
    0 
    )

     

5 Replies

  • rubayatyasmin's avatar
    rubayatyasmin
    Icon for Community Champion rankCommunity Champion

    Your value is in %. That is causing the issue. When dealing with percentage values, keep in mind that they are represented as decimals in DAX calculations. For example, if your "Efficiency % Chg" measure returns a value of 5%, it will be represented as 0.05 in DAX.

     

    try using. <0, 1,0

     

  • Russell93's avatar
    Russell93
    Frequent Visitor

    Hello Rubayatyasmin, thanks so much for your reply but I'm afraid that edit is returning the same results (screenshot below). I did consider the percentage --> decimal relationship as DAX reads it, but I figured anything less than 0 as you suggested would work too:

     

    • rubayatyasmin's avatar
      rubayatyasmin
      Icon for Community Champion rankCommunity Champion

      probably it is returning value as a whole number, not in decimal. try dividing with 100. see if that works.

       

      Eff MoM Conditional =
      IF (
      [Efficiency % Chg] / 100 < 0,
      1,
      0 
      )

       

      • Russell93's avatar
        Russell93
        Frequent Visitor

        Thx again for your help, that's a good idea but I'm afraid it didn't change the output either

        I also tried using parenthesis to isolate the /100 in terms of order of operations hoping that might work, but no changes:

        I will keep researching a solution to this, I think I may have to tell DAX to calculate based on individual Store #'s somehow. Thanks for all of your help