Forum Discussion

AllanBerces's avatar
AllanBerces
Post Prodigy
1 year ago
Solved

Visual Calculation_ Total per Category

Hi good day, Can anyone help me on my visual calculation, i need total sum per category. OUTPUT Thank you
Need Help
  • MohamedFowzan1's avatar
    MohamedFowzan1
    1 year ago

    Hi AllanBerces 

    Please change the measure as follows which would sum up by date:

    Overall Total by Cat. = 
VAR CurrentDate = SELECTEDVALUE(CatSum2[Date])
VAR CurrentGroup = SELECTEDVALUE(CatSum2[Group])
RETURN
    CALCULATE(
        SUM(CatSum2[Current Total Hrs]),
        FILTER(
            ALL(CatSum2),
            CatSum2[Date] = CurrentDate &&
            CatSum2[Group] = CurrentGroup
        )
    )

     

    If you would like to particularly filter for the current date, use the following:

    Overall Total by Cat. = 
VAR FixedDate = TODAY()
VAR CurrentGroup = SELECTEDVALUE(CatSum2[Group])
RETURN
    CALCULATE(
        SUM(CatSum2[Current Total Hrs]),
        FILTER(
            ALL(CatSum2),
            CatSum2[Full Date] = FixedDate &&
            CatSum2[Group] = CurrentGroup
        )
    )

     

     


    If you have a flag column to filter to today's date feel free to use that as well.

    As mentioned for Visual level calculation, you could use:

    VisualCalcTrial =
SUMX(
    WINDOW(
        1,
        ABS,
        -1,
        ABS,
        ROWS,
        ,
        ,
        PARTITIONBY([Group])
    ),
    [Sum of Current Total Hrs]
)


    but again you would have to filter by the date and add the group column


  • MohamedFowzan1's avatar
    MohamedFowzan1
    1 year ago

    Hi AllanBerces 

    For column use either of these DAX for calculated columns based on your requirement:

    OverallTotalByCat_Col 2 = 
VAR CurrentDate = CatSum2[Date]
VAR CurrentGroup = CatSum2[Group]
RETURN
    CALCULATE(
        SUM(CatSum2[Current Total Hrs]),
        FILTER(
            CatSum2,
            CatSum2[Date] = CurrentDate &&
            CatSum2[Group] = CurrentGroup
        )
    )

     


    With fixed date:

    OverallTotalByCat_Col 2 = 
VAR FixedDate = TODAY()
VAR CurrentGroup = CatSum2[Group]
RETURN
    CALCULATE(
        SUM(CatSum2[Current Total Hrs]),
        FILTER(
            CatSum2,
            CatSum2[Full Date] = FixedDate &&
            CatSum2[Group] = CurrentGroup
        )
    )

     
    Mark as solution to guide others!

MohamedFowzan1's avatar
MohamedFowzan1
Super User
1 year ago

Hi AllanBerces 

I believe you could still use the same Group Column and Measure that I have given above as it is still splitting by the Category based on your desired output that is South, North or SouthEast

The calculations above would still be the same
Reference:

And if your only option is to go with Visual Calculation then you will have to use this but include the Group column:

 

 

AllanBerces's avatar
AllanBerces
Post Prodigy
1 year ago

Hi MohamedFowzan1 thank you very much, but the Overall Total by cat should be SouthEast= 44,359.18 and South =10,943.6., sum only of the current date.

  • MohamedFowzan1's avatar
    MohamedFowzan1
    Super User
    1 year ago

    Hi AllanBerces 

    Please change the measure as follows which would sum up by date:

    Overall Total by Cat. = 
VAR CurrentDate = SELECTEDVALUE(CatSum2[Date])
VAR CurrentGroup = SELECTEDVALUE(CatSum2[Group])
RETURN
    CALCULATE(
        SUM(CatSum2[Current Total Hrs]),
        FILTER(
            ALL(CatSum2),
            CatSum2[Date] = CurrentDate &&
            CatSum2[Group] = CurrentGroup
        )
    )

     

    If you would like to particularly filter for the current date, use the following:

    Overall Total by Cat. = 
VAR FixedDate = TODAY()
VAR CurrentGroup = SELECTEDVALUE(CatSum2[Group])
RETURN
    CALCULATE(
        SUM(CatSum2[Current Total Hrs]),
        FILTER(
            ALL(CatSum2),
            CatSum2[Full Date] = FixedDate &&
            CatSum2[Group] = CurrentGroup
        )
    )

     

     


    If you have a flag column to filter to today's date feel free to use that as well.

    As mentioned for Visual level calculation, you could use:

    VisualCalcTrial =
SUMX(
    WINDOW(
        1,
        ABS,
        -1,
        ABS,
        ROWS,
        ,
        ,
        PARTITIONBY([Group])
    ),
    [Sum of Current Total Hrs]
)


    but again you would have to filter by the date and add the group column


    • AllanBerces's avatar
      AllanBerces
      Post Prodigy
      1 year ago

      Hi MohamedFowzan1 techies Ahmedx TomMartens thank you very much for the reply working all good, im just wondering how can i convert this also in calculated column. thank you all

      • MohamedFowzan1's avatar
        MohamedFowzan1
        Super User
        1 year ago

        Hi AllanBerces 

        For column use either of these DAX for calculated columns based on your requirement:

        OverallTotalByCat_Col 2 = 
VAR CurrentDate = CatSum2[Date]
VAR CurrentGroup = CatSum2[Group]
RETURN
    CALCULATE(
        SUM(CatSum2[Current Total Hrs]),
        FILTER(
            CatSum2,
            CatSum2[Date] = CurrentDate &&
            CatSum2[Group] = CurrentGroup
        )
    )

         


        With fixed date:

        OverallTotalByCat_Col 2 = 
VAR FixedDate = TODAY()
VAR CurrentGroup = CatSum2[Group]
RETURN
    CALCULATE(
        SUM(CatSum2[Current Total Hrs]),
        FILTER(
            CatSum2,
            CatSum2[Full Date] = FixedDate &&
            CatSum2[Group] = CurrentGroup
        )
    )

         
        Mark as solution to guide others!