Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Visual Calculation where Values are set to Rows in a Matrix

Hello.

I have a matrix where the member type is the column dimension and my measures are in the values, but the option to  "Swith Values To Rows" is turned on.  

I'd like to create a visual calculation called "Member%"  which is equal to the value of Member divided by the value of (member + non member) for each of the measures.  Is there a way to do that in a visual calcuation, or will I have to create another dax formula for each of the measures?

What I have currently: 

 This columns circled in red are what I'm wishing to add (this was created by someone using a very convoluted sub table and formulas for Member and Non Member; I'm hoping to simplify):

 

Need Help
  • d_m_LNK's avatar
    d_m_LNK
    1 year ago

    I agree with ajaybabuinturi that a calculation group is the solution here.  I recently did the same thing in a very similar situation.  Just in case you need help with a calculation group, SQL BI has a great video here to get you started: https://youtu.be/a4zYT-N-zsU?si=YG5csEdOzkhuorcV

     

    What you will need to do is create the calculation group and recreate your measures you have on the visual right now as calculation items.  Then you can add the column of your calculation group to your matrix visual as a column.  This will give you the result you are wanting to accomplish. 

  • ajaybabuinturi's avatar
    ajaybabuinturi
    1 year ago

    Hi Anonymous,
    As I said above, it's possible with only Calculated groups. I hope you have all the measures which are required to the analysis. You can follow the below steps to get the requirement in an optimised way.


    1.   Go to the Model Tab(1) >> Model (2) >> Click on the Calculation groups ellipsis (...) (3) >> Select New Calculation group >> Rename it as Member(4) >> Add other Calculation Items using ellipsis (...) to the calculated items (5) >> If you want to change the Calculation Item order, just click on the specific item ellipsis (...) (6) and select Move option then define your order.

    Point 3: 

    Point 4:

    Member = CALCULATE(SELECTEDMEASURE(), FILTER(SalesData, SalesData[Member Type]= "Member"))
    Non Member = CALCULATE(SELECTEDMEASURE(), FILTER(SalesData, SalesData[Member Type]= "Non Member"))
    Member Vs Non Member Diff = 
Var Mem = CALCULATE(SELECTEDMEASURE(), FILTER(SalesData, SalesData[Member Type]= "Member"))
Var Non_Mem = CALCULATE(SELECTEDMEASURE(), FILTER(SalesData, SalesData[Member Type]= "Non Member"))
RETURN
Mem - Non_Mem
    Member% = 
Var Mem = CALCULATE(SELECTEDMEASURE(), FILTER(SalesData, SalesData[Member Type]= "Member"))
Var Non_Mem = CALCULATE(SELECTEDMEASURE(), FILTER(SalesData, SalesData[Member Type]= "Non Member"))
RETURN
DIVIDE(Mem, (Mem + Non_Mem), BLANK())

    Point 6:

     

    2.   We need to change the Member% metric format as % by enabling Dynamic format string and set the Format String as "#.#%"


    3.   Drag "calculation groups" in the Columns and "measures" are in the Values. Also Switch values to rows in the "Values" section

     


    Also I am attching working file for your reference. Please let me know if you have any questions. 
    Click here for Calculation_Groups_Working_File 

     

    Thanks,
    If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

7 Replies

  • ajaybabuinturi's avatar
    ajaybabuinturi
    Super User
    1 year ago

    Hi Anonymous,
    We can able to do using calculated groups. Copuld you please provide some sample data and expected output.

     

    • ajaybabuinturi's avatar
      ajaybabuinturi
      Super User
      1 year ago

      Hi Anonymous,
      As I said above, it's possible with only Calculated groups. I hope you have all the measures which are required to the analysis. You can follow the below steps to get the requirement in an optimised way.


      1.   Go to the Model Tab(1) >> Model (2) >> Click on the Calculation groups ellipsis (...) (3) >> Select New Calculation group >> Rename it as Member(4) >> Add other Calculation Items using ellipsis (...) to the calculated items (5) >> If you want to change the Calculation Item order, just click on the specific item ellipsis (...) (6) and select Move option then define your order.

      Point 3: 

      Point 4:

      Member = CALCULATE(SELECTEDMEASURE(), FILTER(SalesData, SalesData[Member Type]= "Member"))
      Non Member = CALCULATE(SELECTEDMEASURE(), FILTER(SalesData, SalesData[Member Type]= "Non Member"))
      Member Vs Non Member Diff = 
Var Mem = CALCULATE(SELECTEDMEASURE(), FILTER(SalesData, SalesData[Member Type]= "Member"))
Var Non_Mem = CALCULATE(SELECTEDMEASURE(), FILTER(SalesData, SalesData[Member Type]= "Non Member"))
RETURN
Mem - Non_Mem
      Member% = 
Var Mem = CALCULATE(SELECTEDMEASURE(), FILTER(SalesData, SalesData[Member Type]= "Member"))
Var Non_Mem = CALCULATE(SELECTEDMEASURE(), FILTER(SalesData, SalesData[Member Type]= "Non Member"))
RETURN
DIVIDE(Mem, (Mem + Non_Mem), BLANK())

      Point 6:

       

      2.   We need to change the Member% metric format as % by enabling Dynamic format string and set the Format String as "#.#%"


      3.   Drag "calculation groups" in the Columns and "measures" are in the Values. Also Switch values to rows in the "Values" section

       


      Also I am attching working file for your reference. Please let me know if you have any questions. 
      Click here for Calculation_Groups_Working_File 

       

      Thanks,
      If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

      • ajaybabuinturi's avatar
        ajaybabuinturi
        Super User
        1 year ago

        Hi Anonymous, we would like to hear your response.

  • d_m_LNK's avatar
    d_m_LNK
    Super User
    1 year ago

    I agree with ajaybabuinturi that a calculation group is the solution here.  I recently did the same thing in a very similar situation.  Just in case you need help with a calculation group, SQL BI has a great video here to get you started: https://youtu.be/a4zYT-N-zsU?si=YG5csEdOzkhuorcV

     

    What you will need to do is create the calculation group and recreate your measures you have on the visual right now as calculation items.  Then you can add the column of your calculation group to your matrix visual as a column.  This will give you the result you are wanting to accomplish. 

  • v-hjannapu's avatar
    v-hjannapu
    Community Support
    1 year ago

    Hi Anonymous ,

    Thank you  for reaching out to the Microsoft fabric community forum.

    I would also take a moment to thank ajaybabuinturi and d_m_LNK  for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

    If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

    Best Regards,
    Harshitha.
    Community Support Team






    • v-hjannapu's avatar
      v-hjannapu
      Community Support
      1 year ago

      Hi Anonymous,

      I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please Accept it as a solution so that other community members can find it easily.


      Best Regards,
      Harshitha.
      Community Support Team.

      • v-hjannapu's avatar
        v-hjannapu
        Community Support
        1 year ago

        Hi Anonymous,

        May I ask if you have resolved this issue? If so, please mark the helpful reply and Accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

        Best Regards,
        Harshitha.
        Community Support Team.