Visual Calculation where Values are set to Rows in a Matrix

[lauriemclolo]

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):

[d_m_LNK · Accepted solution]

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 · Accepted solution]

Hi @lauriemclolo,
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.

[v-hjannapu]

Hi @lauriemclolo ,

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]

Hi @lauriemclolo,

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]

Hi @lauriemclolo,

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.

[d_m_LNK · Accepted solution]

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]

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

[ajaybabuinturi · Accepted solution]

Hi @lauriemclolo,
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 · Accepted solution]

Hi @lauriemclolo, we would like to hear your response.