Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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! 

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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,

     

3 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

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

      • v-lid-msft's avatar
        v-lid-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        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,