cancel
Showing results for
Did you mean:

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

Helper I

## Divide using if , and sum result as sumx

hi,

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

1 ACCEPTED SOLUTION
Super User

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

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.

Helper I

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  !

Super User

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.

Helper I
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

Helper I

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

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors