Percent of row total of count values

[mariannacarbo]

Hi all,

Im very new using Power Bi. I have a database showing corrective maintenance (PM02) in diferente range of time since them were opened. The values ar from database calulated with count (distinct) I want to show in a matrix the percent of row total but shows always 100%. At the next image, I'm showing the matrix representing the percent of column total. I need this measure but in row. Anyone can help me, please?

Thanks in advance.

[AllisonKennedy]

@mariannacarbo In order to get Percent of Row total to work, you must put something in the 'Columns' section of the matrix visualization. You appear to be using many different measures in the 'Values' section, but nothing in 'Columns' section. Therefore the % of Row Total always shows as 100%

If you can paste a sample of first 10 or so rows of raw data and explain what percent value you want displayed as the row total (or do the calculation in Excel and show us the formulas), then we can help you with the DAX for this. 

Do you want the percent as weeks? You could try putting Week Number in Columns and Show Values on Rows turn that to 'On' by clicking the format paint roller when the matrix visual is selected. 

[mariannacarbo]

Hi @AllisonKennedy 

I tried putting the weeks in the field of colums but it shows to 100%. At the following tables I show you in Excel what I need:

The visual show this in no calculation.	The visual that I need in order to do conditional format
	PM02 ON TIME	PM02 - 30	PM02 - 60	PM02 - 90		WEEK	PM02 ON TIME	PM02 - 30	PM02 - 60	PM02 - 90
30		4				30		100%
32		3				32		100%
33	1	2				33	33%	67%
34	3	3				34	50%	50%
35	5	12				35	29%	71%

This 10 rows of the raw data:

G51482

00242157

10001049

20001087

PM02

"Hand truck"#497607 se apaga

0047-010

BEVERAGE BASE|CIDRA

2.0

0.000

NEW

REL  NMAT PRC  SETC

0010

E

09/09/2021

MF1

PRF980

P19

09/09/2021

09/10/2021

9/12/2021

3

On Time

A58781

50019621

10000420

20000238

PM02

04-026 no da marcha

0047-050-020-570

64-FORK LIFT NO.04-026 CROWN

FORK LIFT CROWN FC-4040-60-TT188

0.0

0.000

NEW

REL  PRT  GMPS MACM PRC  SETC

0010

E

09/06/2021

MF1

PRF980

P19

08/06/2021

09/06/2021

9/12/2021

36

30

E40922

00000000

10000151

20000261

PM02

33393585 SALIDERO DE AGUA FRIA EN COIL G

0047-040-070-000-200

08-AHU MIXING ROOM 2

UNIT AIR HANDLING TRANE MCCB035VA0A0VA

0.0

0.000

NEW

REL  MACM PRC  SETC

0010

E

07/29/2021

MF1

PRF110

P19

07/27/2021

07/29/2021

9/12/2021

45

45

E40922

00000000

10000170

20000300

PM02

33406649 5S TALLER MECANICOS DE MANUFACT

0047-060-010-060-340

59-SHOP WELDING PRODUCTION

0.0

0.000

NEW

REL  GMPS MACM PRC  SETC

0010

E

07/29/2021

MP1

PRM620

P19

07/27/2021

07/29/2021

9/12/2021

45

45

E40922

00060112

10000152

20000262

PM02

33445324 Carrito de PIAB para el mezzani

0047-020-080

29-MEZZANINE 3

2.0

0.000

NEW

REL  MACM PRC  SETC

0010

E

09/05/2021

MP1

PRM620

P01

07/27/2021

09/05/2021

9/12/2021

45

45

E40922

00000000

10000153

20000263

PM02

33462353 REPARACION DE CHILLER #3 O68857

0047-040-040-020

57-CHILLER TRANE NO.3 CVHE045 350 TONS

CHILLER TRANE CVHE045

0.0

0.000

NEW

REL  MACM PRC  SETC

0010

E

07/29/2021

MF1

PRF110

P19

07/27/2021

07/29/2021

9/12/2021

45

45

G67716

00048116

10000764

20000842

PM02

33505619 TORQUIMETRO  SERAC 1 SIN BOTON

0047-030-030-450

14-TORQUE TESTER

TORQUE TESTER KAPS-ALL EB-650A

4.5

0.000

WEC

TECO CNF  GMPS JBFI MACM PRC  SETC

0010

E

09/09/2021

08/24/2021

MP1

PRM100

P19

08/24/2021

09/09/2021

9/12/2021

18

On Time

E40922

00000000

10000154

20000264

PM02

33517231 REEMPLAZO DE ASCO TUBE G67708

0047-010-000-120

09-CENTRIFUGE POD CE-09-138-00

CENTRIFUGE APV B10,POD#2

0.0

0.000

NEW

REL  MACM PRC  SETC

0010

E

09/05/2021

MP1

PRM620

P19

07/27/2021

09/05/2021

9/12/2021

45

45

E40922

00000000

10000190

20000320

PM02

33517886 SANDBLASTING Y PINTURA SUPPLY F

0047-040-070-080-000

32-SUPPLY FAN TWIN CITY 1

FAN SUPPLY TWIN CITY  SF # 1

0.0

0.000

NEW

REL  MACM PRC  SETC

0010

E

07/29/2021

MF1

PRF100

P19

07/27/2021

07/29/2021

9/12/2021

45

45

O68857

00000000

10000447

20000397

PM02

33552614 PM - LEV CLEANNING - A

0047-040-070-040-080

10-DUST COLECTOR TORIT NO.9 B/BASE

COLLECTOR DUST TORIT DF-3DF-6

0.0

0.000

NEW

REL  GMPS NMAT PRC  SETC

0010

E

09/14/2021

MF1

PRF110

P19

08/11/2021

09/14/2021

9/12/2021

31

30

G29677

01010944

10000357

20000377

PM02

33564570 REPARACION EN TRASFORMADOR G514

0047-040-060-050

ELECTRICAL SYSTEM NO.5 8.2 KV

0.0

0.000

NEW

REL  MACM PRC  SETC

0010

E

07/31/2021

MF1

PRF301

P19

07/29/2021

07/31/2021

9/12/2021

43

30

[AllisonKennedy · Accepted solution]

Thanks @mariannacarbo for the additional info and sample data - do you have column headers and table name please? 

See attached file below signature - % of row total works fine for me in my test, I have also included a DAX measure you can use to acheive the same result: 

% of Row Total On Time Status =

VAR _RowTotal = CALCULATE(COUNT('Table'[Task ID]), ALL('Table'[On Time Status]))

RETURN

DIVIDE(COUNT('Table'[Task ID]), _RowTotal)

[mariannacarbo]

@AllisonKennedy , I have another doubt.  I need to format conditionally in base of the percent presented in each of the time range in the row of th e weeks.  I don't see how to create diferent  rules at the  4 different option of time range.

At the following table I show you how I want to view the color of the values.

[AllisonKennedy]

@mariannacarbo  Have you resolved this yet?

You can try with DAX: 

% Format Color =
SWITCH( SELECTEDVALUE(table[On Time Status]

, "On Time", IF( [% of Row Total On Time Status] <=.4, "Red", "Green")

, "30", IF( [% of Row Total On Time Status] <=.3, "Red", "Green")

, "60", IF( [% of Row Total On Time Status] <=.2, "Red", "Green")

, "90", IF( [% of Row Total On Time Status] <=.1, "Red", "Green")

Then apply conditional formatting to 'Field Value' and select the % Format Color measure.

[mariannacarbo]

Thank you very much @AllisonKennedy !!!  Its worked perfectly!! 🙂