Forum Discussion

Charu's avatar
Charu
Icon for Post Patron rankPost Patron
8 years ago

Groupby function with the IF statement in power bi

Hi Everyone,

 

Help me to write the DAX formula for the below logic

IF(DIVIDE(Ntable[VOLUME_ACTUAL],Ntable[VOLUME_TARGET],0)>1,1,DIVIDE(Ntable[VOLUME_ACTUAL],Ntable[VOLUME_TARGET],0)) * VolumeWSscore group by Vertical

 

VolumeWSscore this column is from another table called WStable(created the relationship between Ntable and WStable using Vertical column(many to one)

 

Sample WStable data

Vertical | Volumetarget

A |    30%

B |    40%

C | 40%

D | 40%

E | 40%

 

how to add the highlighted logic in IF statement.

Please advise.

Thanks in advance

Need Help

1 Reply

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    8 years ago

    Hi Charu,

     

    Try DAX formula like below:

     

    Result =
IF (
    DIVIDE ( Ntable[VOLUME_ACTUAL], Ntable[VOLUME_TARGET], 0 ) > 1,
    1,
    DIVIDE ( Ntable[VOLUME_ACTUAL], Ntable[VOLUME_TARGET], 0 )
)
    * RELATED ( WStable[VolumeWSscore] )

    Regards,

    Jimmy Tao