DAX formula Not working

[sixathur]

Hi,

I am trying to create a DAX measure where an existing measure named "TOTAL VISITS", if total visits is more than 58, multiply the difference between TOTAL VISITS and 58 by 15. For example: Total visits = 60, 60-58 =2, then 2(15) =30. The idea is do that for every week in the given dataset then total all the data that is multiplied by 2. Right now when I use IF statement or VAR statements, it's totaling ALL the visits not just visits more than 58 and multiplying that with 15. Is it because I am using a Measure (TOTAL VISITS) instead of an actual column?

Thank you.

[v-pbandela-msft]

Hi @sixathur,

I wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.
If the community member's answer your query, please mark it as "Accept as Solution" and select "Yes" if it was helpful.
If you need any further assistance, feel free to reach out.

Thank you,
Pavan.

[v-pbandela-msft]

Hi @sixathur,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, kindly "Accept  as  Solution" and give it a 'Kudos' so others can find it easily.

Thank you,
Pavan.

[v-pbandela-msft]

Hi @sixathur,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please "Accept  as  Solution" and give a 'Kudos' so other members can easily find it.

Thank you,
Pavan.

[johnbasha33]

Hi @sixathur 
You're on the right track, and the issue likely stems from how measures work in Power BI. Since TOTAL VISITS is a measure, it dynamically evaluates based on the current filter context, which means you need to carefully control its aggregation to ensure only visits above 58 are counted.
try this
Extra_Visit_Cost =
SUMX(
VALUES('YourTable'[Week]), -- Iterate over each week
VAR VisitCount = [TOTAL VISITS] -- Get total visits for the current week
RETURN IF(VisitCount > 58, (VisitCount - 58) * 15, 0)
)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[sixathur]

Hi,

Thank you for your help, the formula worked but when I use the card visual for this measure, its still giving me the total of the visits*15, not the difference*15.

[mdaatifraza5556]

Hi @sixathur 

Please try my above DAX. 
belowis the snapshot which i have tried

For_Visits_more_than_58 =

    VAR Visits_Above_58 =

    SUMX(

        VALUES('Date'[weekno]),

        MAX(

            [TotalVisits] - 58,

            0

        ) * 15

    )

    RETURN

        Visits_Above_58

--------------------------------------------
If total visits is less than 60 then it shows 0

Please accept it as a solution if you got your answer.

[sixathur]

Hi, 

I do not have a week number slicer, my slicer is based on the start of the week date.

[v-pbandela-msft]

Hi @sixathur,

Thank you for reaching out in Microsoft Community Forum.

Thank you @tamerj1  @mdaatifraza5556  @johnbasha33  for the helpful response.

Please follow below steps to resolve the error;

1.Adjust the [TotalVisits] measure to respect the weekly context (start of the week date). Use this DAX:

TotalVisits =
CALCULATE(
COUNTROWS(visits),
ALLEXCEPT('Date', 'Date'[WeekStartDate])
)

2.Extra Visit Cost Measure:

For_Visits_More_Than_58 =
SUMX(
VALUES('Date'[WeekStartDate]), -- Iterate over each week start date
VAR WeeklyVisits = [TotalVisits]
VAR ExtraVisits = WeeklyVisits - 58
RETURN IF(ExtraVisits > 0, ExtraVisits * 15, 0)
)

3. Please use visuals like below

->Use a Matrix visual to verify the logic per week.

->The Card visual will show the aggregated total, but check per week first in Matrix.

Please continue using Microsoft community forum.

If you found this post helpful, please consider marking it as "Accept as Solution" and give it a 'Kudos'. if it was helpful. help other members find it more easily.

Regards,
Pavan.

[tamerj1]

@sixathur 

What is the dax of TotalVisits measure?

[sixathur]

CALCULATE(countrows(visits))

[mdaatifraza5556]

Hi @sixathur 

Can u please try the below DAX.

For_Visits_more_than_58 =
VAR Visits_Above_58 =
SUMX(
VALUES('Table'[Week]), 
MAX( [TOTAL VISITS] - 58, 0 ) * 15
)
RETURN
Visits_Above_58

If you find this solution usefull then accept it as solution.

[sixathur]

Hi,

Thank you for your help, the formula worked but when I use the card visual for this measure, its still giving me the total of the visits*15, not the difference*15.