Card visualisation % of total instead of count

[siddrow]

Hi

I need help creating a measure for a card. I need to count rows from a query column that contain the words "Completed", "Not Required" and "Not Yet Due" and then work out what percentage that is of the total of rows in that column (that column also contains words "Overdue and in Progress" and "Overdue and Not Yet Started").

E.g. the below makes up 100% and I would only want to show the percentage of the grand total on the card for completed, not required and not yet due, which would be 93%.

"Completed" = 28%

"Not Required" = 2%

"Not Yet Due" = 63%

"Overdue and in Progress" = 1%

"Overdue and Not Yet Started" = 6%

[Ashish_Mathur · Accepted solution]

Hi,

Drag the Status column to a Table visual and filter the visual on the 3 specific entries.  Write these measures:

Count = countrows(Data)

Completed count = calculate([count],data[status]="Completed")

Completed Count (%) = divide([completed count],[count])

Drag the third measure to a card visual.  Write measures 2 and 3 for the other status as well.

Drag the second 

[Ashish_Mathur]

Use a multi card visual

[siddrow]

I ended up creating another measure that added the 3 measures to give me 1 percentage as I don't want a multi card. Thanks for your help 

[Ashish_Mathur]

You are welcome.

[TheoC]

Hi @siddrow 

You can use the below measure to get what you're after:

Filtered % = 

VAR _1 = CALCULATE ( SUM ('Table'[Amount] ) , NOT 'Table'[Status] in { "Overdue and in Progress" , "Overdue and Not Yet Started" } )
VAR _2 = _1 / CALCULATE ( SUM ('Table'[Amount] ) , ALL ( 'Table' ) )

RETURN

_2

The output is as per below:

Hope this helps! 

Theo 

[TheoC]

Hi @siddrow 

You can use the below measure to get what you're after:

Filtered % = 

VAR _1 = CALCULATE ( SUM ('Table'[Amount] ) , NOT 'Table'[Status] in { "Overdue and in Progress" , "Overdue and Not Yet Started" } )
VAR _2 = _1 / CALCULATE ( SUM ('Table'[Amount] ) , ALL ( 'Table' ) )

RETURN

_2

The output is as per below:

Hope this helps! 🙂

Theo 

[siddrow]

This doesn't help sorry. I am not looking to create a table visualisation as this will affect my current dashboard layout. There are no numbers to count, only text within that query column. It needs to count total number of rows in that column and then give me the percentage of rows that have the text "completed", "not yet due" and "not required"

[TheoC · Accepted solution]

@siddrow just change the SUM ( Table[Column] ) to COUNTROWS ( 'Table' ) and it should be exactly what you want. Here it is below:

Filtered % = 

VAR _1 = CALCULATE ( COUNTROWS ( Table ) , NOT 'Table'[Status] in { "Overdue and in Progress" , "Overdue and Not Yet Started" } )
VAR _2 = _1 / CALCULATE ( COUNTROWS ( 'Table' ) , ALL ( 'Table' ) )

RETURN

_2

[Ashish_Mathur · Accepted solution]

Hi,

Drag the Status column to a Table visual and filter the visual on the 3 specific entries.  Write these measures:

Count = countrows(Data)

Completed count = calculate([count],data[status]="Completed")

Completed Count (%) = divide([completed count],[count])

Drag the third measure to a card visual.  Write measures 2 and 3 for the other status as well.

Drag the second 

[siddrow]

Hi

Thanks for the reply. I do not want to create a table visual for this as it will affect my current dashboard layout. Is there no other way of doing it without having to show an additional visualisation?

[Ashish_Mathur]

I did not ask you to create a Tabel visual at all.  Read my reply carefully.

[siddrow]

I can only drag one measure into the card visualisation field, so how would I be able to total the completed, not yet due and not required in one card visual to give me a percentage?