Forum Discussion
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 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.
12 Replies
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.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.
Hi sixathur
You're on the right track, and the issue likely stems from how measures work in Power BI. SinceTOTAL VISITSis 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 !!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.
Hi sixathur
Please try my above DAX.
belowis the snapshot which i have triedFor_Visits_more_than_58 =VAR Visits_Above_58 =SUMX(VALUES('Date'[weekno]),MAX([TotalVisits] - 58,0) * 15)RETURNVisits_Above_58--------------------------------------------
If total visits is less than 60 then it shows 0
Please accept it as a solution if you got your answer.
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.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.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.
