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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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