PowerBi returing the max value instead of the Sum

[davidjimenez18]

Hi, I'm creating a Attendance Points report on Power Bi, the points are being assesed correctly but I can't get a summary for each agent. Can someone assist?

*Time Missed*

Attendance Time Missed =

VAR Late = Calculate(Sum(DEO_TU_Sch[Hours Decimals]),Filter(DEO_TU_Sch,DEO_TU_Sch[Scheduled Activity] = "Late"))

Var Left_Early = Calculate(Sum(DEO_TU_Sch[Hours Decimals]),Filter(DEO_TU_Sch,DEO_TU_Sch[Scheduled Activity] = "Left Early"))

Var Absent_Calledin = Calculate(Sum(DEO_TU_Sch[Hours Decimals]),Filter(DEO_TU_Sch,DEO_TU_Sch[Scheduled Activity] = "Absent - called in"))

Var NCNS = Calculate(Sum(DEO_TU_Sch[Hours Decimals]),Filter(DEO_TU_Sch,DEO_TU_Sch[Scheduled Activity] = "No Call No Show"))

Return

IF(NCNS > 0 , "NCNS",late+Left_Early+Absent_Calledin+NCNS)

*Point Assesment*

Attendance Points Assesments =

Var Tier1 = IF(AND('Mesuares Libary'[Attendance Time Missed] >= 0.02, 'Mesuares Libary'[Attendance Time Missed] <=0.98), 0.2, blank())

Var Tier2 = IF(AND('Mesuares Libary'[Attendance Time Missed] >= 0.99, 'Mesuares Libary'[Attendance Time Missed] <=1.98), 0.4, blank())

Var Tier3 = IF(AND('Mesuares Libary'[Attendance Time Missed] >= 1.99, 'Mesuares Libary'[Attendance Time Missed] <=2.98), 0.6, blank())

Var Tier4 = IF(AND('Mesuares Libary'[Attendance Time Missed] >= 2.99, 'Mesuares Libary'[Attendance Time Missed] <=3.98), 0.8, blank())

VAR Tier5 = IF('Mesuares Libary'[Attendance Time Missed] >= 3.99, 1, blank())

Var Tier6 = IF('Mesuares Libary'[Attendance Time Missed] = "NCNS", 4, blank())

Return

(Tier1+Tier2+Tier3+tier4+Tier5+Tier6)

*Cumulative Points*

Total Cumulative Points =

CALCULATE (

[Attendance Points Assesments],

FILTER( ALLSELECTED( DEO_TU_Sch[Date] ),

'DEO_TU_Sch'[Date] <= Max('DEO_TU_Sch'[Date])

)

)

As you can see on the screenshot, the point are being assesed correctly I cant see how many points does the agent have as of today (including everything earned)

I know this

[amitchandak]

@davidjimenez18 , Try like

Total Cumulative Points =
CALCULATE (
sumx(values('DEO_TU_Sch'[Date]), [Attendance Points Assesments]),
FILTER( ALLSELECTED( DEO_TU_Sch[Date] ),
'DEO_TU_Sch'[Date] <= Max('DEO_TU_Sch'[Date])
)
)

[davidjimenez18]

Thank you for your response @amitchandak, maybe I'm doing something wrong. I see total are now summing on  the Matrix Table only on the first row but not on the actual points. Do you think it could be an error on the formulas that I have or maybe a data error?

[amitchandak]

@davidjimenez18 , try like

Total Cumulative Points =
CALCULATE (
sumx(values('DEO_TU_Sch'[Date]), [Attendance Points Assesments]),
FILTER( ALLSELECTED( DEO_TU_Sch),
'DEO_TU_Sch'[Date] <= Max('DEO_TU_Sch'[Date])
)
)

or better to have joined date tbale

Total Cumulative Points =
CALCULATE (
sumx(values('DEO_TU_Sch'[Date]), [Attendance Points Assesments]),
FILTER( ALLSELECTED( Date ),
'Date'[Date] <= Max('Date'[Date])
)
)

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[davidjimenez18]

Hey @amitchandak,

Here is a report sample to see if you can help me further. I would love it if you could help me as well on the "Point Assessment" for the NCNS because the system is assessing 5 points instead of 4 on the formula.

I have created 3 pages 1 with each calculation. Thank you again for your help!

https://www.dropbox.com/s/vbtjp89ygz6wjdg/Report%20Sample.pbix?dl=0 

[v-chenwuz-msft]

Hi @davidjimenez18 ,

Is this you want?

Maybe you can try this code:

Total Cumulative Points 4 =
VAR _a =
    SUMMARIZE (
        'FACT_Scheduled Activities',
        'FACT_Scheduled Activities'[Agent Name],
        [Date],
        "c",
            CALCULATE (
                SUMX (
                    VALUES ( 'FACT_Scheduled Activities'[Date] ),
                    [Attendance Points Assesments]
                ),
                FILTER (
                    ALLSELECTED ( 'FACT_Scheduled Activities'[Date] ),
                    'FACT_Scheduled Activities'[Date]
                        <= EARLIER ( 'FACT_Scheduled Activities'[Date] )
                )
            )
    )
RETURN
    SUMX ( _a, [c] )

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[davidjimenez18]

Hi, @v-chenwuz-msft , Yes, that solves one of my questions, thank you.

The other one is that I need the points to be accumulated as the come. 

[v-chenwuz-msft · Accepted solution]

Hi @davidjimenez18 ,

Total Cumulative Points 4 =
VAR _a =
    SUMMARIZE (
        'FACT_Scheduled Activities',
        'FACT_Scheduled Activities'[Agent Name],
        [Date],
        "c",
            CALCULATE (
                SUMX (
                    VALUES ( 'FACT_Scheduled Activities'[Date] ),
                    [Attendance Points Assesments]
                ),
                FILTER (
                    ALLSELECTED ( 'FACT_Scheduled Activities'[Date] ),
                    'FACT_Scheduled Activities'[Date]
                        <= EARLIER ( 'FACT_Scheduled Activities'[Date] )
                )
            )
    )
VAR _b =
    SUMX ( _a, [c] )
VAR _c =
    SUMX (
        SUMMARIZE (
            CALCULATETABLE (
                'FACT_Scheduled Activities',
                FILTER (
                    ALLSELECTED ( LKUP_Dates[Date] ),
                    [Date] <= SELECTEDVALUE ( LKUP_Dates[Date] )
                )
            ),
            [Agent Name],
            [Date],
            "c",
                CALCULATE (
                    [Attendance Points Assesments],
                    FILTER (
                        ALL ( LKUP_Dates ),
                        [Date] <= EARLIER ( 'FACT_Scheduled Activities'[Date] )
                    )
                )
        ),
        [c]
    )
RETURN
    IF (
        HASONEVALUE ( 'FACT_Scheduled Activities'[Date] ),
        IF ( ISBLANK ( [Attendance Points Assesments] ), BLANK (), _c ),
        _b
    )

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.