topic Re: Insert a diferent calculated measure in a Matrix in DAX Commands and Tips

Insert a diferent calculated measure in a Matrix

jonatan_ap — Sat, 15 Apr 2023 04:58:39 GMT

Hi,

I have the following Matrix with Questions and Answers as columns and rows headers (2 different tables).

If I export the table I have this:

I'm trying to insert the percentage per question beside each count of question*answer like shown in the table below highlighted in yellow

Is that something doable in Power BI?

Re: Insert a diferent calculated measure in a Matrix

Wilson_ — Sat, 15 Apr 2023 13:44:25 GMT

Hello Jonatan,

Yes, this is doable in Power BI.

Assuming this is the only table in your data model, the measure would look something like this:

AnswerPercentage = VAR TotalAnswers = CALCULATE ( SUM ( Table1[#] ), REMOVEFILTERS ( Table1[Answers] ) ) RETURN DIVIDE ( SUM ( Table1[#] ), TotalAnswers )

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

Re: Insert a diferent calculated measure in a Matrix

jonatan_ap — Sat, 15 Apr 2023 18:02:10 GMT

Hi Wilson,

I actually have 2 tables, 1 for Questions (it's a fact table so contains more than just questions but to be simple let's call it Questions) and 1 for Answers, and in the Questions table I have the reference Answer Key to only 1 Answer as only 1 Answer can be picked per Question. The solution you proposed works fone if I have only 1 table. I appreciate the time you spend in this question. Thanks.

Re: Insert a diferent calculated measure in a Matrix

Wilson_ — Sat, 15 Apr 2023 19:46:53 GMT

Jonatan,

In that case, I suspect there's a chance the measure would still work if you change the REMOVEFILTER to instead be REMOVEFILTERS ( Answers[Answer] ).

If that doesn't work, can you share a screenshot of the Model view from your pbix? Also, does the matrix row come from the Questions fact table and the matrix column from the Answers (dimension?) table?

Re: Insert a diferent calculated measure in a Matrix

jonatan_ap — Sat, 15 Apr 2023 21:53:24 GMT

Hi Wilson,

It didn't work, returned 1 for all cells:

I created the measure like this:

AnswerPercentage =

VAR TotalAnswers =

CALCULATE (

SUM ( 'fact QA_Evaluations_Questions_Answers_Fact'[Answer_Id_Selected] ),

REMOVEFILTERS ( 'dim QA_Answers_Selected_Dim'[Answer_Key] )

)

RETURN

DIVIDE (

SUM ( 'fact QA_Evaluations_Questions_Answers_Fact'[Answer_Id_Selected] ),

TotalAnswers

)

This is the model:

Re: Insert a diferent calculated measure in a Matrix

Wilson_ — Sat, 15 Apr 2023 23:22:33 GMT

Jonatan,

What if you turn off bi-directional filtering between the two tables? Generally speaking, one-to-many relationships should only go in one direction.

Re: Insert a diferent calculated measure in a Matrix

jonatan_ap — Mon, 17 Apr 2023 19:03:39 GMT

Thank you Wilson,

After reading and reading in Google for possible solutions, I found that there is a native option called Percent of Row total (see screenshot below) that does exactly what I was looking for, I just needed to put the same field as a Value and select this option and that was it:

This is the final result:

Thank you for your help!

Re: Insert a diferent calculated measure in a Matrix

Wilson_ — Mon, 17 Apr 2023 21:18:56 GMT

Excellent, glad you figured it out jonatan. 😄