Forum Discussion
Visual Calculation where Values are set to Rows in a Matrix
- 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.
- 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_MemMember% = 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_FileThanks,
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. - 1 year ago
Hi Anonymous, we would like to hear your response.
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.