Need Correct Total

[AbhishekPandey]

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]

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

[amitchandak]

@AbhishekPandey , You have to try like

Assuming your current measure is Measure

New measure = sumx(values(Table[unique ID]),[Measure])

[AbhishekPandey]

@amitchandak If you see the description again, i have tried this but not getting the correct result.

[amitchandak]

@AbhishekPandey ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[AbhishekPandey]

There is a difference of environment, when i tried implementing the solutuion on a blank PBI with some dummy data it worked fine for me, but showing a blank value when i replicate the same on a live AAS environment. 

Measure = 
VAR Check =
    DIVIDE (
        SUM ( 'Tab'[Month of Engagement] ),
        SUM ( Tab[Months In Contract L30] )
    )
RETURN
    IF (
        HASONEVALUE ( 'Tab'[ID] ),
        IF ( Check >= 0.75 && Check <= 1.00, Check, 0 ),
        CALCULATE (
            SUMX (
                CALCULATETABLE (
                    tab,
                    FILTER (
                        ALL ( tab ),
                        VAR __Check =
                            DIVIDE ( 'Tab'[Month of Engagement], Tab[Months In Contract L30] )
                        RETURN
                            __Check < 1
                            && __Check > 0.75
                    )
                ),
                1
            )
        )
    )

[AbhishekPandey]

Can anyone help in this. 

[Anonymous]

[Measure] =
COUNTX(
    FactTable,
    var __value =
        // This division, since it's performed
        // on an individual row level, should be
        // stored in a column in the fact table.
        // This value would then only be retrieved
        // from the column instead of calculated.
        // This way the measure would be much faster.
        DIVIDE(
            // If something is qualified with the name
            // of a table, it's a column. If it's not
            // qualified, it's a measure. This is the
            // convention in DAX that every DAX dev
            // follows.
            FactTable[Month of Engagement],
            FactTable[Months In Contract L30],
            0
        )
    var __test =
        and(
            0.75 <= __value,
            __value <= 1.00
        )
    return
        DIVIDE( __test, __test )
)

[AbhishekPandey · Accepted solution]

@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))