How to get Total Count and its percentage using Gauge chart

[saivina2920]

I am trying to create Gauge chart using Check Box condition.  condition is below.

But, i want to do it it percentage using the below formula.

Sample file attached for reference. https://1drv.ms/u/s!AiSRcgO5FUmN8V1vE_wfYPDNkxsM?e=Yu5pjz 

If check box enabled ==>
A count = Total count of (REGION-A,REGION-B,REGION-C,REGION-D) which contains "TC" or "SUCCESS" value
B count (100%) = Count of Total unique values of column A
Total % = (A / B) * 100

If check box disabled
A count = Total count of (REGION-A,REGION-B,REGION-C,REGION-D) which contains "TC" or "SUCCESS" value & "REGION" and "VALUES" column blank
B count (100%) = Count of Total unique values of column A
Total % = (A / B) * 100

The above two condition display based on the category(Any one is mandatory to select and not possible to select more than one category).

How to achieve this using Gauge chart...

[Anonymous]

Hi @saivina2920 ,

I updated your sample pbix file(see attachment), please check whether that is what you want.

1. Create a checkbox table using Enter data method and apply the Checked field on the slicer

2. Create a measure as below to get the percentage

Total % = 
VAR _selchecked =
    SELECTEDVALUE ( 'Checkbox'[Checked] )
VAR _part =
    CALCULATE (
        DISTINCTCOUNT ( 'Sheet1'[RECID] ),
        FILTER (
            'Sheet1',
            ( 'Sheet1'[REGION-A]
                IN { "TC", "SUCCESS" }
                || 'Sheet1'[REGION-B]
                IN { "TC", "SUCCESS" }
                || 'Sheet1'[REGION-C]
                IN { "TC", "SUCCESS" }
                || 'Sheet1'[REGION-d] IN { "TC", "SUCCESS" } )
                && (
                    IF (
                        _selchecked = "Y",
                        1 = 1,
                        ISBLANK ( 'Sheet1'[REGION] ) && ISBLANK ( 'Sheet1'[VALUE] )
                    )
                )
        )
    )
VAR _all =
    CALCULATE ( DISTINCTCOUNT ( 'Sheet1'[RECID] ), ALL ( 'Sheet1' ) )
RETURN
    DIVIDE ( _part, _all, 0 )

If the above one not working, please provide the calculation logic and correct result base on current table data. Thank you.

Best Regards

[saivina2920]

Thanks for your help.

below is the updated data and logic for Gauge chart and file for reference : https://1drv.ms/u/s!AiSRcgO5FUmN8WCEO1Lj0wC1UtmR?e=MoJqcv 

If check box checked
Step-1 : unique total row count of RECID for selecting category ==> Result : selecting TYPE-A and its RECID count="10" Rows
==> Here Step-1 final value "10" is 100% of the chart values.
==> Now, these 100% has to be categorised in 3 different color with 50% (red),25% (orange) and 25% (green) proportion.
Step-2 : Total row count of (REGION-A,REGION-B,REGION-C,REGION-D) which contains "TC" or "SUCCESS" ==> Result = "3" Rows
==> Now Step-2 values is "3". so its percentage is (3/10)*100 ==> 30%
Step-3 : Now the speedometer should point @ 30% (step-2) (refer image)

If check box not checked,
everything same in the above which some additional conitions needs to be added in Step-2
Step-1 : unique total row count of RECID for selecting category ==> Result : selecting TYPE-A and its RECID count="10" Rows
==> Here Step-1 final value "10" is 100% of the chart values.
==> Now, these 100% has to be categorised in 3 different color with 50% (red),25% (orange) and 25% (green) proportion.
Step-2 : Total row count of (REGION-A,REGION-B,REGION-C,REGION-D) which contains "TC" or "SUCCESS" & "REGION" and "VALUES" column
should be blank ==> Result = "2" Rows
==> Now Step-2 values is "2". so its percentage is (2/10)*100 ==> 20% is the success and So out of 100% only 20% is success and it turns Green color
Step-3 : Now the speedometer should point @ 20% (step-2)

[Anonymous]

Hi @saivina2920 ，

You can update the formula of measure [Total %] as below:

Total % =

VAR _selchecked =

SELECTEDVALUE ( 'Checkbox'[Checked] )

VAR _part =

CALCULATE (

DISTINCTCOUNT ( 'Sheet1'[RECID] ),

FILTER (

'Sheet1',

( 'Sheet1'[REGION-A]

IN { "TC", "SUCCESS" }

&& 'Sheet1'[REGION-B]

IN { "TC", "SUCCESS" }

&& 'Sheet1'[REGION-C]

IN { "TC", "SUCCESS" }

&& 'Sheet1'[REGION-d] IN { "TC", "SUCCESS" } )

&& (

IF (

_selchecked = "Y",

1 = 1,

ISBLANK ( 'Sheet1'[REGION] ) && ISBLANK ( 'Sheet1'[VALUE] )

)

)

)

)

VAR _all =

CALCULATE ( DISTINCTCOUNT ( 'Sheet1'[RECID] ), ALLSELECTED ( 'Sheet1' ) )

RETURN

DIVIDE ( _part, _all, 0 )

Conditional formatting in Fill data colors

And make conditional formatting for Fill data colors as below screenshot, you can refer the following blog:

Sentiment Colors for Gauge Visual in Power BI

You can get all details in the attachment.

Best Regards

[saivina2920]

You are rocking..All is fine.

One small doubt,

Is it possible to show the all 3 colors by default in the Gauge Chart.?

based on the percentage speedometer sholud point there in exact color as we already defined..

Is it possible...?

[Anonymous · Accepted solution]

Hi @saivina2920 ,

I'm afraid the default Gauge visual can't achieve customized colors by value range. You can use custom visual Tachometer to replace the original Gauge visual and set it up as shown below to achieve your needs. Please find the details in the attachment.

Best Regards

[saivina2920]

Pls. Update when you are free...

[saivina2920]

Hi @Anonymous,

As you I am using filter for "Y" or "N" option.

Instead of using Y or N option, i am using filter one value "REMOVE REGION AND VALUE".

we don't want to use "Y or "N". we can directly use ""REMOVE REGION AND VALUE".

The text ""REMOVE REGION AND VALUE"" is equivalent to "Y".

I tried to change the DAX expression based on the above. But, it showing only one value.

If i click means (Checked), it should consider the REGION AND the value.

If i unchecked (By Default), it should not be consider the REGION AND the value. ==> this is not working

What is the problem in my expression.

pls. find the latest file : https://1drv.ms/u/s!AiSRcgO5FUmN8WLFmnAxIy0Lpk5K?e=pqaj6j 

This is urgent pls. help.

[Anonymous]

Hi @saivina2920 ，

Please update the formula of measure [Total New %] as below, you can find the details in the attachment.

Total New % =

VAR _part =

CALCULATE (

DISTINCTCOUNT ( 'Sheet1'[RECID] ),

FILTER (

'Sheet1',

( 'Sheet1'[REGION-A]

IN { "TC", "SUCCESS" }

&& 'Sheet1'[REGION-B]

IN { "TC", "SUCCESS" }

&& 'Sheet1'[REGION-C]

IN { "TC", "SUCCESS" }

&& 'Sheet1'[REGION-d] IN { "TC", "SUCCESS" } )

&& (

IF (

ISFILTERED('Checkbox-2'[Checked]),

1 = 1,

ISBLANK ( 'Sheet1'[REGION] ) && ISBLANK ( 'Sheet1'[VALUE] )

)

)

)

)

VAR _all =

CALCULATE ( DISTINCTCOUNT ( 'Sheet1'[RECID] ), ALLSELECTED ( 'Sheet1' ) )

RETURN

DIVIDE ( _part, _all, 0 )

Best Regards

[saivina2920]

Thanks and it's really awesome results.

one last query, Some times if the condition not satisfied and the result showing "[Blank]" values. How to handle "[Blank]" values.

If anything [Blank], is it possible to show it's "0%" (Zero Percentage).

[Anonymous]

Hi @saivina2920 ,

You can add +0 to the end of the formula for measure [Total New %] directly, as in the following screenshot.

Best Regards

[saivina2920]

can pls. share the test file. i am getting something wrong result if i add 0 (zero) in the formula...

[saivina2920]

Achieved...It's done now...

One normal question : Any Easy steps to deriving calculation and fomulas using DAX queries from the complex requirements...because, we have the data, but, we do not aware some easy steps and queries to easily achieve the task like the above requirement.

[saivina2920]

I just confusing to handle check box cases..pls. let me know if you need any more details.