Insert a diferent calculated measure in a Matrix

[jonatan_ap]

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?

[jonatan_ap]

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.

[Wilson_]

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?

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

[Wilson_]

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

[jonatan_ap]

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:

[Wilson_]

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.

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

[jonatan_ap · Accepted solution]

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!

[Wilson_]

Excellent, glad you figured it out jonatan. 😄