Rolling up values within a grouping within the same table...

[Anonymous]

Hi All,

I want to rollup a value say FM (Fiscal Month) costs where I have a grouping in a Matrix visualuzation coming from the same table. So

for example:

customer              contract         FM01         FM02       FM03

navy  stores        1030                 5                   0                   200

                                                                0                    100            10

The goal is:

navy stores         1030                 5                   100            210

Thanks! 

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can use the following measure to meet your requirement:

Measure =
VAR cus =
    MAX ( 'Table'[contract] )
RETURN
    IF (
        cus = BLANK (),
        BLANK (),
        CALCULATE(
            SUM('Table'[value]),
            ALL('Table'[contract])
    )
)

Then we can get the result like this,

If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to OneDrive For Business and share the link here.

Best regards,

[Anonymous]

Why are you using the MAX function on a charactor string?

[v-lid-msft]

Hi @Anonymous ,

The purpose we use MAX function here is to get the value of the current filter context.

Because column names cannot be called directly in a measure. So We use the MAX function to get the value of current column. We can also use MIN function, they have the same role in this question.

Here is a simple maybe help you to understand.

BTW, pbix as attached.

Best regards,