Data value and Percent value both in Matrix Table

Data Visualizations
1

Hello Team,

Kindly find the attached PBIX file where am looking to 1. Combine Data values & Percent values in same column(Image) and also show Grand Total in the left side instead of Right, Can you please help me to achieve these 2 things in Matrix table ?
Percent_Test.pbix (28.3 KB)

image

Thank You,

2

Hello @Dharma,

Thank You for posting your query onto the Forum.

Below is the measure alongwith the screenshot of the final results provided for the reference -

Combined Measure = 
VAR _Total_Values = 
COUNTROWS( Table1 )

VAR _Percentage_Of_Total_Values = 
FORMAT(
    DIVIDE( 
        COUNTROWS( Table1 ) , 
        CALCULATE( COUNTROWS( Table1 ) , 
            ALL( Table1[Country] ) ) , 
        0 ) , 
    "(0%)"
)

RETURN
_Total_Values & " " & _Percentage_Of_Total_Values

Final Results

I’m also attaching the working of the PBIX file for the reference.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Note: Once you write this measure, they’re converted into the “Text” format and cannot be mapped into the graphical representations apart from Table or Matrix visual.

Thanks and Warm Regards,
Harsh

Percent_Test - Harsh.pbix (30.4 KB)

3 Likes
3

Hi @Dharma,

Just noticed @Harsh beat me to it, with a great solution… But if order in your visual is that important you could achieve this by creating separate measures one for the Total and one for each Department… A pattern similair to this should do the job.

Total = 
VAR _Count =
    CALCULATE( [Values], REMOVEFILTERS( Table1[Department] ))
VAR _PCT =
    FORMAT(
        CALCULATE( [Value PCT], REMOVEFILTERS( Table1[Department] )),
        "(0%)"
    ) 
RETURN
    _Count & " " & _PCT

image

By the way where you having trouble calculating the percentage?
You did create an explicit measure for the Value but an implicit measure for the Value %.

Value PCT = 
DIVIDE( [Values], CALCULATE( [Values], ALLEXCEPT( Table1, Table1[Department] )) )

I hope this is helpful.

4

Hi @Melissa and @Harsh, thank you for always providing this group with great solutions. We greatly appreciate your efforts and your share of knowledge. :slight_smile:

@Dharma, did the responses above help you solve your query?

If not, how far did you get and what kind of help you need further?

If yes, kindly mark as solution the answer that solved your query.

1 Like