Display row totals as percentages in a table or matrix

[jaxmurphy]

I'm trying to rereate a pivot table a coworker made in Excel in Power BI, but I'm having a hard time determining whether I should be using a table or a matrix, and how to get the values to come in cleanly looking like the pivot table. Here's the pivot table:

And here's a snippet of the data I'm trying to work with:

I want the final visual to look as much like the pivot table as possible. But when I try to make measures to calculate percentages, things get wonky.

I'm pretty new to Power BI, so I think there's something fundamental that I'm not getting here. This is the dax query I wrote for that first column:

Percent Mentor Meets = CALCULATE(
    COUNTROWS(G1_Final_Eval),
    G1_Final_Eval[Role]="Mentor" && G1_Final_Eval[Punctuality_and_Obligations]="Meets Expectations") 
    / 
    CALCULATE(COUNTROWS(G1_Final_Eval),G1_Final_Eval[Role]="Mentor")

Clearly I've got something wrong. I've been able to get the percentages to land in the right spot on the table, but never with a correct total, and usually with the adjacent columns all wrong. I imagine this isn't as complicated as I'm making it out to be, but I'm just stuck. Can anyone help me out?

[Anonymous · Accepted solution]

Hi @jaxmurphy 

The reason your measure is calculated incorrectly is that you may have overlooked the fact that fields in a visual are automatically filters for that visual. You can refer to add-a-filter-to-a-visual .

Therefore, your measure need to be adjusted as follows:
Remove G1_Final_Eval[Punctuality_and_Obligations]=“Meets Expectations” in the numerator, and use the ALL() function to remove filters in the denominator.

Mentor = 
DIVIDE(
    CALCULATE(
        COUNTROWS(G1_Final_Eval),
        G1_Final_Eval[Role] = "Mentor"
    ) + 0,
    CALCULATE(
    COUNTROWS(G1_Final_Eval),
    G1_Final_Eval[Role] = "Mentor",
    ALL(G1_Final_Eval)
    ),
    0
)

Supervisor = 
DIVIDE(
    CALCULATE(
        COUNTROWS(G1_Final_Eval),
        G1_Final_Eval[Role] = "Supervisor"
    ) + 0,
    CALCULATE(
    COUNTROWS(G1_Final_Eval),
    G1_Final_Eval[Role] = "Supervisor",
    ALL(G1_Final_Eval)
    ),
    0
)

Grand Total = 
DIVIDE(
    COUNTROWS(G1_Final_Eval) + 0,
    CALCULATE(
    COUNTROWS(G1_Final_Eval),
    ALL(G1_Final_Eval)
    ),
    0
)

Check the measure to change its format to “%”.

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dharmendars007]

Hello @jaxmurphy , 

Please try to modify your DAX like the below 

Percent Mentor Meets =
DIVIDE(
CALCULATE(
COUNTA(Q_Final[Rank]),
Q_Final[Role] = "Mentor",
Q_Final[FinalPunctuality and Obligation] = "Meets Expectation"),
CALCULATE(
COUNTA(Q_Final[Rank]),Q_Final[Role] = "Mentor"),0)

1. DIVIDE Function: It’s safer to use DIVIDE since it handles division by zero gracefully.

2. Check Data Relationships: Ensure your table relationships are correctly set up so that context transitions (filters) work as expected. If Q_Final is related to other tables, ensure that the relationships are active and correct.
3.Table or Matrix Visual? If your goal is to mimic an Excel pivot table, the matrix visual might be the better option. It allows for row and column grouping, much like a pivot table
4. Grand Totals: If the issue lies with incorrect totals or aggregates in the matrix, you might need to create a specific measure for totals. In some cases, measures behave differently at the grand total level in a matrix.

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[jaxmurphy]

I'm very confused by your use of Q_FINAL and RANK, here. I do not have a table with that name, and none of my tables contain a column named RANK.