Measure with multiple conditions

[NadiaLR1234]

Good day

I have the following measures

• DT_RevenueSCG = IFERROR([TargetDayRevenueSCG],0)
• DailyTargetRevenueSCG = MAX(TTVBudget[DT_RevenueSCG])*Max(vw_TTV_REN[MaxDate2])
• TargetDayRevenueSCG = sum(TTVBudget[LeisureRevenue])/max(DIM_DATE[NumberOfDaysInTheMonth])
• MaxDate2 = LOOKUPVALUE(DIM_DATE[DayOfMonth],DIM_DATE[DateFull],vw_TTV_REN[DocCreationDate])

However I want to say in TargetDayRevenueSCG when SalesChannelGroup = Leisure then sum(TTVBudget[LeisureRevenue])/max(DIM_DATE[NumberOfDaysInTheMonth]), if SalesChannelGroup = MICE/Logistics then sum(TTVBudget[MiceRevenue])/max(DIM_DATE[NumberOfDaysInTheMonth]), if SalesChannelGroup = Transient then sum(TTVBudget[TransientRevenue])/max(DIM_DATE[NumberOfDaysInTheMonth]), if SalesChannelGroup = Traveller Enablement then sum(TTVBudget[TERevenue])/max(DIM_DATE[NumberOfDaysInTheMonth]), else 0 - but have been trying all sort of ways to do this but it is not doing what I want to display please help on how I must structure it - table join are on MonthKey  

[v-tejrama]

Hi @NadiaLR1234,

The TargetDayRevenueSCG measure calculates daily target revenue based on the selected sales channel group. It picks the correct monthly revenue from the TTVBudget table and divides it by the number of days in the selected month using the DIM_DATE table. This way, the daily target updates automatically based on the selected channel and date.

Here’s the DAX measure I used:

TargetDayRevenueSCG =

VAR DaysInMonth = MAX(DIM_DATE[NumberOfDaysInTheMonth])

VAR SCG = SELECTEDVALUE(vw_TTV_REN[SalesChannelGroup])

VAR Revenue =

    SWITCH(

        SCG,

        "Leisure", SUM(TTVBudget[LeisureRevenue]),

        "MICE/Intelligence", SUM(TTVBudget[MICEResidential]),

        "Travel/Intelligence", SUM(TTVBudget[TransientRevenue]),

        "Traveller Enablement", SUM(TTVBudget[TERevenue]),

        0

    )

RETURN

    DIVIDE(Revenue, DaysInMonth, 0)

Find attached .PBIX file for your reference.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you,
Tejaswi.

[NadiaLR1234]

Hi

It is still not working can I perhaps send you my pbix file on email- could it be the joins ?

[v-tejrama]

Hi @burakkaragoz,

Thanks for reaching out.

Unfortunately, I can't receive files through email. But if you'd like, you can upload your pbix file here directly, and I’ll take a look. It might be an issue with the joins, so I’ll check the relationships and anything else that could be causing the problem.

Looking forward to helping you sort it out.

[NadiaLR1234]

Unfortunately I can not add it on a public platform due to the confidentialy of the data - I will just play around with the table joins

Thank you

[NadiaLR1234 · Accepted solution]

I managed to get it to work without the date divide 

thank you 

TargetDayRevenueSCG =

VAR SCG = SELECTEDVALUE(vw_TTV_REN[SalesChannelDescGroup])

VAR Revenue =

    SWITCH(

        SCG,

        "Leisure", SUM(TTVBudget[LeisureRevenue]),

        "MICE/Logistics", SUM(TTVBudget[MiceRevenue]),

        "Transient", SUM(TTVBudget[TransientRevenue]),

        "Traveller Enablement", SUM(TTVBudget[TERevenue]),

        0

    )

RETURN

    Revenue

[burakkaragoz]

Hi @NadiaLR1234 ,

You’re on the right track, but to make TargetDayRevenueSCG dynamic based on SalesChannelGroup, you’ll want to use a SWITCH or IF structure inside a measure that evaluates the current context. Here’s a pattern that should work:

TargetDayRevenueSCG = 
VAR DaysInMonth = MAX(DIM_DATE[NumberOfDaysInTheMonth])
RETURN
    SWITCH(
        SELECTEDVALUE(SalesChannelGroup),
        "Leisure", SUM(TTVBudget[LeisureRevenue]) * DaysInMonth,
        "MICE/Intelligence", SUM(TTVBudget[MICEResidential]) * DaysInMonth,
        "Travel/Intelligence", SUM(TTVBudget[TransientRevenue]) * DaysInMonth,
        BLANK()
    )

Make sure:

• Your SalesChannelGroup column is properly related to the budget table.
• You’re using this measure in a visual that has SalesChannelGroup in the context (like in rows or filters).
• If you're joining on MonthKey, ensure that both your budget table and date table are aligned on that key.

Let me know if you need help adjusting this to work with SUMX or if your model has more complexity.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

[NadiaLR1234]

Maybe I must do the matrix different ? left is the matrix - right is to check if values are correct as you see now all are blank ?