Forum Discussion

NadiaLR1234's avatar
NadiaLR1234
Helper I
1 year ago
Solved

Measure with multiple conditions

Good day   I have the following measures   DT_RevenueSCG = IFERROR([TargetDayRevenueSCG],0) DailyTargetRevenueSCG = MAX(TTVBudget[DT_RevenueSCG])*Max(vw_TTV_REN[MaxDate2]) TargetDayRevenueSCG ...
Need Help
  • NadiaLR1234's avatar
    NadiaLR1234
    1 year ago

    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
v-tejrama's avatar
v-tejrama
Community Support
1 year ago

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's avatar
NadiaLR1234
Helper I
1 year ago

Hi

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

  • v-tejrama's avatar
    v-tejrama
    Community Support
    1 year ago

    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's avatar
      NadiaLR1234
      Helper I
      1 year ago

      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's avatar
        NadiaLR1234
        Helper I
        1 year ago

        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