Need correct total, Power BI table

0
votes

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.

2
powerbidax

2 Answers

0
votes

For Total, you should change 3th parameter of IF to:

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

Below my test example (for testing only I have put check variable in first IF IF ( Check >= 0.75 && Check <= 1.00, Check, 0 ) to see if check calculation is correct):

enter image description here

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

Measure = VAR Check = DIVIDE ( [Month of Engagement] , [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 ( [Month of Engagement], [Months In Contract L30] ) RETURN __Check >=0.75 && __Check <=1.00 ) ), 1 ) ) )

Here are the brief definitions

Month of Engagement:= CALCULATE(COUNTROWS( SUMMARIZE('table','table'[MONTH_OF_ENGAGEMENT],"Count",DISTINCT('table'[MONTH_OF_ENGAGEMENT]))),'table'[FREQUENCY_FLAG]="Y")+0

Here MONTH_OF_ENGAGEMENT is value extracted from a date as YYYYMM

Months In Contract L30:= Var DBRefreshDate30= [Database Refresh Date]-30 RETURN DATEDIFF(DBRefreshDate30,[Database Refresh Date],MONTH)+1

Now basis on dividing Month of Engagement from Months In Contract L30, i am deriving a percentage, and checking if that percentage lies between 0.75 and 1.00