Forum Discussion

pauliuseg's avatar
pauliuseg
Icon for Helper I rankHelper I
5 years ago
Solved

Divide using if , and sum result as sumx

hi, i made example : Revenue, Revenue target. and if target reached more than 100%, then Bonus need to be paid. table in sigle colums shows ok : bonus reached in Jan and Feb, in Repairs. and last ...
Need Help
  • AlB's avatar
    AlB
    5 years ago

    pauliuseg 

    A couple of things:

    1. You should always use a well-formed calendar table. It must have full years. See here: https://dax.guide/functions/time-intelligence/

    2. I would strongly recommend not to use the Auto date/time function

     

    If you do not use the Auto date/Time function, add a new column to your calendar table with the month name and create this measure based on the one you already have:

     

    Measure = 
SUMX (
    CROSSJOIN (
        DISTINCT ( 'Calendar'[MonthName] ),
        DISTINCT ( Service_type[Service_type] )
    ),
    [bonus payout]
)

     

     

    If you want to keep using the Auto date/time function, you can use this measure, again based on the one you already have:

     

    Measure V2 = 
SUMX (
    CROSSJOIN (
        DISTINCT ( 'Calendar'[Date].[Month] ),
        DISTINCT ( Service_type[Service_type] )
    ),
    [bonus payout]
)

     

    See it all at work in the attached file. 

     

    		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

     

selimovd's avatar
selimovd
Icon for Most Valuable Professional rankMost Valuable Professional
5 years ago

Hey pauliuseg ,

 

you can use if in the SUMX argument.

Try the following measure:

Bonus payout =
SUMX(
    'Revenue',
    IF(
        'Revenue'[Revenue] <> BLANK() && 'Revenue'[Revenue Target] <> BLANK(),
        'Revenue'[Bonus],
        BLANK()
    )
)

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

 

  • pauliuseg's avatar
    pauliuseg
    Icon for Helper I rankHelper I
    5 years ago

    hi, looks good, but  issues, first, you maybe missed table names, as Revenue target is in Targets table, so i corrected, and then i get what i and got before.  so formula is  (from 3 tables..)

    Bonus payout =
    SUMX(
    'Revenue',
    IF(
    'Revenue'[Revenue] <> BLANK() && 'Targets'[Revenue Taget] <> BLANK(),
    'Bonus'[Bonus if reached target],
    BLANK()
    ) 
    )

    and the error...

    in upper post i have added the file, if anybody would look like to look at issue ..