Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
pauliuseg
Helper I
Helper I

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)

pauliuseg_0-1622141222053.png

here is simple model : 

pauliuseg_1-1622141383773.png

thaks a lot !

as stucked here for hours .. 😄

 

1 ACCEPTED SOLUTION
AlB
Super User
Super User

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

 

SU18_powerbi_badge

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.

 

View solution in original post

6 REPLIES 6
AlB
Super User
Super User

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

 

SU18_powerbi_badge

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.

 

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
Super User
Super User

Hi @pauliuseg 

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

 

SU18_powerbi_badge

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
Super User
Super User

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
 

 

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

pauliuseg_0-1622181464597.png

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.