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 colum says 50 and 60 eur.

but how to get Total 110 eur, not 180. as 180 is total available bonuses.

should be used sumx , but it't more than one table in calcualtion : 

3 tables (revenue, targets, bonuses)

now bonus payout formula is 

bonus payout = if(DIVIDE(SUM('Revenue'[Revenue]), SUM('Targets'[Revenue Taget]))>1,sum(Bonus[Bonus if reached target]),0)
but total is not ok .. (last column)

here is simple model : 

thaks a lot !

as stucked here for hours .. 😄

 

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.

     

6 Replies

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

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi pauliuseg 

    Can you share the pbix where you built the mock model?

     

    		 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.

     

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    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.

     

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

      Whow !

      first - thanks for explanations, yes, this time table  i just created for test purpose to be as close as possible to my real file/model.

      second - thanks a lot for solution, i was so stucked in this place , and could not move on, as this figure was needed in few places.

      thanks a lot , spending a time and writing formula  !