Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
davidjimenez18
Frequent Visitor

PowerBi returing the max value instead of the Sum

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])
)
)
 
davidjimenez18_0-1650220235587.png


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

1 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:

vchenwuzmsft_0-1650530370792.png

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.

 

View solution in original post

7 REPLIES 7
amitchandak
Super User
Super User

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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?

 

davidjimenez18_0-1650382245811.png

 

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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 

Hi @davidjimenez18 ,

Is this you want?

vchenwuzmsft_1-1650435359538.png

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.

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. 

davidjimenez18_0-1650456347196.png

 

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:

vchenwuzmsft_0-1650530370792.png

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.

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.