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

     

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  !