Forum Discussion

sixathur's avatar
sixathur
Frequent Visitor
1 year ago

DAX formula Not working

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...
sixathur's avatar
sixathur
Frequent Visitor
1 year ago

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's avatar
mdaatifraza5556
Super User
1 year ago

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's avatar
    sixathur
    Frequent Visitor
    1 year ago

    Hi, 

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

    • tamerj1's avatar
      tamerj1
      Community Champion
      1 year ago

      sixathur 

      What is the dax of TotalVisits measure?

      • sixathur's avatar
        sixathur
        Frequent Visitor
        1 year ago

        CALCULATE(countrows(visits))

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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.