Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Grouping calculated measures as values in a matrix

Hi,   I current have a table as follows;     Category A Category B Category C Category D Value 1 1 1 1 1 Value 2 1 1 1 1 Value 3 1 1 1 1 Value 4 1 1 1 1 Valu...
Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    Hi Anonymous ,

     

    Create a table with the following format:

    Measure    Group

    Value 1 Group 1
    Value 2 Group 1
    Value 3 Group 2
    Value 4 Group 2
    Value 5 Group 3
    Value 6 Group 3

     

    Now add the following measure:

    Grouping Calculation = 
VAR Group_calculation =
    SWITCH (
        SELECTEDVALUE ( 'Grouping_Measures'[Group] );
        "Group 1"; [Value 1] + [Value 2];
        "Group 2"; [Value 3] + [Value 4];
        "Group 3"; [Value 5] + [Value 6]
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Grouping_Measures'[Measure] );
        "Value 1"; [Value 1];
        "Value 2"; [Value 2];
        "Value 3"; [Value 3];
        "Value 4"; [Value 4];
        "Value 5"; [Value 5];
        "Value 6"; [Value 6];
        Group_calculation
    )

     

    Format your matrix with the following way:

    • Rows:
      • Grouping_Measures[Group]
      • Grouping_Measures[Measure]
    • Columns
      • Table[Category]
    • Values
      • [Groupin Calculation]

     

    See attach result.

     

     

MFelix's avatar
MFelix
Icon for Super User rankSuper User
6 years ago

Hi Anonymous ,

 

Create a table with the following format:

Measure    Group

Value 1 Group 1
Value 2 Group 1
Value 3 Group 2
Value 4 Group 2
Value 5 Group 3
Value 6 Group 3

 

Now add the following measure:

Grouping Calculation = 
VAR Group_calculation =
    SWITCH (
        SELECTEDVALUE ( 'Grouping_Measures'[Group] );
        "Group 1"; [Value 1] + [Value 2];
        "Group 2"; [Value 3] + [Value 4];
        "Group 3"; [Value 5] + [Value 6]
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Grouping_Measures'[Measure] );
        "Value 1"; [Value 1];
        "Value 2"; [Value 2];
        "Value 3"; [Value 3];
        "Value 4"; [Value 4];
        "Value 5"; [Value 5];
        "Value 6"; [Value 6];
        Group_calculation
    )

 

Format your matrix with the following way:

  • Rows:
    • Grouping_Measures[Group]
    • Grouping_Measures[Measure]
  • Columns
    • Table[Category]
  • Values
    • [Groupin Calculation]

 

See attach result.

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thanks for the quick reply.  Is it possible to complete the first step given that the values are the results of calculated measures I have created?

     

    Thanks

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      6 years ago

      Hi Anonymous ,

       

      The values that I have created in my file are several measures named Value 1, ..., Value 6. I assumed that what you mean by calculated measures right?

       

      Can you please explain what you mean by complet the first step?

       

      Regards,

      MFelix

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi,

         

        My apologies for not being very clear.  The groupings are what I hope to create using the calculated measures that I currently have as measures.  For example, Grouping values 1 & 2 into Group 1, so that I can use the groupings as rows.  Hope this makes sense.

         

        Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi, me again!

     

    Firstly, thanks very much for your help with the solution to my problem.  I have an additional step to this that I need some help with...

     

    I have successfully created the groups within the table, and now would like to conditionally format some fo the values in the groups based on the results.  The problem I have is that because I'm using just the one measure, based on the above, I can't conditionallly format any of the individual rows using the conditional formatting section of the format tab.  I'm guessing I going to have to add some additional DAX to the existing grouping measure, or possibly amend the original measure that the grouping DAX references?

     

    Any idea how I could get aroudn this.

     

    Hope this makes sense.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hello, thanks for the solution!
    My measures are different types and want to format some of them as a whole number and some of them as a percentage. Is it possible? I tried to do it in your sample file but can't get the results.

    Thanks in advance!

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      I've found the solution, maybe someone else needs it. So in measures the function FORMAT(measure, "Percent") works fine. Any measure can have own format in this way.

  • JLebens's avatar
    JLebens
    Frequent Visitor
    4 years ago

    Hi MFelix,

     

    Thank you for sharing the solution to this problem. 

    This looks like exactly what I'm looking for, but creating the Grouping Calculation measure in my report results in an infinite "Working on it". Do you have any suggestions as to how to prevent this? 

     

    Thanks in advance. Kind regards. 

    • MFelix's avatar
      MFelix
      Icon for Super User rankSuper User
      4 years ago

      Hi JLebens ,

       

      Witouht any further details about what you are calculating is difficult to suggest anything.

       

      What are the the calculations you are making?