Visual Calculation_ Total per Category

[AllanBerces]

Hi good day,

Can anyone help me on my visual calculation, i need total sum per category.

OUTPUT

Thank you

[MohamedFowzan1 · Accepted solution]

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

[Ahmedx]

pls try 

[techies]

Hi @AllanBerces please try this 

custom = COLLAPSE([Total amount], ROWS)

[AllanBerces]

Hi @techies thank you very much for the reply, but it total the total group not total by category

[techies]

Hi @AllanBerces please check this

[TomMartens]

Hey @AllanBerces ,

if you want to use a Visual Calculations you can use this formula:

Calculation = SUMX( WINDOW( 1, ABS, -1, ABS, ROWS,,,PARTITIONBY( [Cat] )), [Current Total Hrs])

This is how it looks like in my sample report:

Hopefully, this helps to tackle your challenge.

Regards,
Tom

[MohamedFowzan1]

Hi @TomMartens 

I still find this a limitation when we are not including the Group Column like he has asked in the output above.

From my understanding, with visual-level calculations, you can only partition or group by columns included in the visual. Measures and expressions are not accepted for PARTITIONBY in WINDOW, so the only way it would be possible to create this measure in visual level calculation:

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

is by adding the Group Calculated column I have mentioned above and then adding this visual calculation measure.

Haven't seen any workaround so believing its a limitations as of now. 

[MohamedFowzan1]

Hi @AllanBerces 

Kindly do the following:

Created the sample table:

Add this as a calculated column: 

Group = 
VAR dashPos = SEARCH("--", [Cat],, 0)
RETURN IF(dashPos > 0, LEFT([Cat], dashPos - 1), BLANK())

Then, create the following measure:

Total per Group = 
CALCULATE(
    SUM(CatSum[Current Total Hrs]),
    FILTER(
        ALL(CatSum),
        CatSum[Group] = SELECTEDVALUE(CatSum[Group])
    )
)

You will not have to add the Group column to the visual

With visual-level calculations, you can only partition or group by columns included in the visual. Measures and expressions are not accepted for PARTITIONBY in WINDOW, so the only way it would be possible to create a this measure in visual level calculation:

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

is by adding the Group Calculated column I have mentioned above and then adding this visual calculation measure.

Once the Group column is removed, you would notice an error:

[AllanBerces]

Hi @MohamedFowzan1 @TomMartens thank you very much for the reply, but mistake on my side. the current total be duplicate according to my plan date.

DESIRED OUTPUT

Thank you

[MohamedFowzan1]

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]

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 · Accepted solution]

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]

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 · Accepted solution]

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!

[AllanBerces]

Hi @MohamedFowzan1 thank you very much working perfectly