how to calculate total sum and maximum used screen count in power bi?

Question

How to calculate the total count with respect to enabled date and maximum used screen count with its name

My Input table

Date        Name        Id  Count   Screen  Enabled Date
03-01-2020  Karthikeyan 1   4       Login   03-01-2020
03-01-2020  Karthikeyan 1   3       Logout  03-01-2020
05-01-2020  Karthikeyan 1   6       Logout  03-01-2020
05-01-2020  Sumit       2   9       Login   05-01-2020
05-01-2020  Sumit       2   5       Logout  05-01-2020

My code is

enabledDateSummary = 
ADDCOLUMNS(
    ADDCOLUMNS(
            SUMMARIZE(
                enabledDate,
                enabledDate[Id],
                enabledDate[Name],enabledDate[Enabled Date]
            ),
        "TotalCount", CALCULATE( SUM( enabledDate[Count] ), FILTER(enabledDate, 
        enabledDate[Date] = MIN(enabledDate[Enabled Date])),
        ALLEXCEPT(enabledDate, enabledDate[Enabled Date], enabledDate[Name])), 
        "ScreenCount", CALCULATE( MAX( enabledDate[Count] ), 
        ALLEXCEPT(enabledDate,enabledDate[id]) )
    ),
    "Screen",
    VAR CurrentCount = [ScreenCount]
    RETURN CALCULATE( MIN(enabledDate[Screen]), enabledDate[Count] = CurrentCount )
)

Output is

Expected Output table

score 0 · Accepted Answer · answered Jan 12 '20 at 14:51

I have resolved the issue by using SUMX() function instead of SUM()

enabledDateSummary

enabledDateSummary = 
ADDCOLUMNS(
    ADDCOLUMNS(
    FILTER(
            SUMMARIZE(
                enabledDate,
                enabledDate[Id],
                enabledDate[Name], enabledDate[Enabled Date]
            ),enabledDate[Enabled Date] <> DATE(1970,01,01)
        ),
        "TotalCount", CALCULATE(SUMX(FILTER(enabledDate, 
        enabledDate[Date] = enabledDate[Enabled Date]),[Count])), 
        "ScreenCount", CALCULATE( MAX( enabledDate[Count] ) )
    ),
    "Screen",
    VAR CurrentCount = [ScreenCount]
    RETURN CALCULATE( MIN(enabledDate[Screen]), enabledDate[Count] = CurrentCount )
)

enabledDateSummary Output:

how to calculate total sum and maximum used screen count in power bi?

1 Answers1