Forum Discussion

jonatan_ap's avatar
jonatan_ap
Icon for Helper I rankHelper I
3 years ago
Solved

Insert a diferent calculated measure in a Matrix

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's avatar
    jonatan_ap
    3 years ago

    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!

     

7 Replies

  • Wilson_'s avatar
    Wilson_
    Icon for Memorable Member rankMemorable Member
    3 years ago

    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's avatar
      jonatan_ap
      Icon for Helper I rankHelper I
      3 years ago

      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_'s avatar
        Wilson_
        Icon for Memorable Member rankMemorable Member
        3 years ago

        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's avatar
    jonatan_ap
    Icon for Helper I rankHelper I
    3 years ago

    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_'s avatar
      Wilson_
      Icon for Memorable Member rankMemorable Member
      3 years ago

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