Forum Discussion

vijay273162's avatar
vijay273162
Icon for Helper III rankHelper III
4 years ago
Solved

IF condition HELP

HI In below table, I need to return 3rd row "GM" Company with "1" in [IF 80%] measure. I write DAX as.... [IF 80 %] = IF([**bleep**. % of Total] <0.8,1,0)   Note : [**bleep**. % of Total] & [Ran...
power bi
  • V-lianl-msft's avatar
    V-lianl-msft
    4 years ago

    Hi vijay273162 ,

     

    Try to create a measure like below:

    IF 80% = 
VAR MIN_ = MINX(ALL('Table'),ABS('Table'[cum %of total]-0.8))
VAR VALUE_ = CALCULATE('Table'[cum %of total],FILTER(ALL('Table'),ABS('Table'[cum %of total]-0.8)=MIN_))
RETURN IF([cum %of total]<=VALUE_,1,0)

     


    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

VahidDM's avatar
VahidDM
Icon for Super User rankSuper User
4 years ago

HI vijay273162 

 

Do you want to change the condition or just add an exception for GM?

 

To change the condition:

[IF 80 %] = IF([**bleep**. % of Total] <0.84,1,0)

 

to add an exception :

[IF 80 %] = IF(Max(Company])="GM",1,[**bleep**. % of Total] <0.84,1,0))

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

 

 

 

  • vijay273162's avatar
    vijay273162
    Icon for Helper III rankHelper III
    4 years ago

    Thanks for reply.

     

    If I apply filter, with IF condition, it return top 2 rows.

    This will give me [% of total] row total as 61.11%. (only volvo & ford).

    I need to get close to 80% or more as row total, (next row "GM" to get row total as 83.33 %.)

     

    • V-lianl-msft's avatar
      V-lianl-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi vijay273162 ,

       

      Try to create a measure like below:

      IF 80% = 
VAR MIN_ = MINX(ALL('Table'),ABS('Table'[cum %of total]-0.8))
VAR VALUE_ = CALCULATE('Table'[cum %of total],FILTER(ALL('Table'),ABS('Table'[cum %of total]-0.8)=MIN_))
RETURN IF([cum %of total]<=VALUE_,1,0)

       


      Best Regards,
      Liang
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.