Search code examples
powerbipowerbi-desktoppowerbi-embeddedpowerbi-datasourcepowerbi-custom-visuals

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


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

enter image description here

Expected Output table

enter image description here


Solution

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

    enter image description here