Divide using if , and sum result as sumx

[pauliuseg]

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

[AlB · Accepted solution]

@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]

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  !

[AlB]

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.

[pauliuseg]

sure, with pleasure

https://drive.google.com/file/d/1sQSbljOFvJvuaG6XYl7i39F-pKZm1pzW/view?usp=sharing

[selimovd]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[pauliuseg]

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