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.

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

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