Forum Discussion

AbhishekPandey's avatar
AbhishekPandey
Icon for Helper I rankHelper I
5 years ago
Solved

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 i...
  • AbhishekPandey's avatar
    AbhishekPandey
    5 years ago

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


AbhishekPandey's avatar
AbhishekPandey
Icon for Helper I rankHelper I
5 years ago

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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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

  • AbhishekPandey's avatar
    AbhishekPandey
    Icon for Helper I rankHelper I
    5 years ago

    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's avatar
      AbhishekPandey
      Icon for Helper I rankHelper I
      5 years ago

      Can anyone help in this. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago
        [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 )
)