Forum Discussion
Need Correct Total
I have a measure in which i am dividing total number of contractual months from total number of months. I am getting correct result, but the total in the bottom is not correct.
The first column is a unique ID, Third and fourth columns are numerator and denominator, Second column is the result of the division, I want to count those IDs, where the division is between 0.75 and 1.00
Here are my calculations
Var Check=DIVIDE([Month of Engagement],[Months In Contract L30])
RETURN
IF(HASONEVALUE('Fact - TABLE'[ID]),IF(Check>=0.75 && Check<=1.00,DISTINCTCOUNT(ID),0),SUMX('Fact - TABLE',IF(Check>=0.75 && Check<=1.00,DISTINCTCOUNT(ID),0)))
Please let me know, how to solve this.
Anonymous Thanks for the suggestion i got the fix, this time not using the variable anywhere in the expression.
This i have added in the else part of hasonevalue.
SUMX(
FILTER(
VALUES('Table'[id]),divide(Month of engagement, Months in Contract L30)>=0.75 && divide(Month of engagement, Months in Contract L30)<=1.00),1))
8 Replies
AbhishekPandey , You have to try like
Assuming your current measure is Measure
New measure = sumx(values(Table[unique ID]),[Measure])
amitchandak If you see the description again, i have tried this but not getting the correct result.
AbhishekPandey ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
- What is the structure of your fact table? If you perform a calculation on an individual row level of the fact table, then move the calculation to a column, do not use DAX to do it on the fly, use Power Query or do it in the source. This is the first piece of advice. Secondly, variables in DAX are constant, once they've been defined, they do not change (DAX is a functional language).
