Gauge cumulative values and targets

[Anonymous]

Hi,

I have imported two tables : Validations and Goal_Values.

The table Validations contains following columns:

The column [ID] contains an unique key

The column [Validation_Date] contains dates between 01/01/2017 and 25/09/2018. These values are not unique.

After import, 3 new columns were added:

Validation_Year = YEAR(Validations[Validation_Date])

Validation_Month = MONTH(Validations[Validation_Date])

Validation_Month_Year = FORMAT(MONTH(Validations[Validation_Date]);"00") & "/" & FORMAT(YEAR(Validations[Validation_Date]);"0000")

The table Goal_Values contains following columns:

The table contains one record for every month in the years 2017 and 2018. In total 24 records.
Every record containing the goal of the number of validations per month that should be reach or should have been reached.

After import, 1 new columns was added:

Goal_Month_Year = FORMAT(Goal_Numbers[Goal_Month];"00") & "/" & FORMAT(Goal_Numbers[Goal_Year];"0000")

A new table is created that contains per month the number of validations:

The 3 tables are linked:

Validations[Validations_Month_Year] * —1 Goal_Numbers[Goal_Month_Year]

Validations_Numbers[Validations_Month_Year] * —1 Goal_Numbers[Goal_Month_Year]

In the Visualisations I have 2 slicers:

• List with Field Goald_Numbers[Goal_Year]

• Less than or equal too with field Goald_Numbers[Goal_Month]
  The less than and equal too will make a cumulative sum per month

Questions:

If I want to add a Gauge visualization where I can see a cumulative number of validations per month and per year, and as target the cumulative goal numbers per month and per year, what is best practice: using Sum of Validation_Numbers[Validation_Number] or the Count of Validations[ID] as value and Sum of Goal_Numbers[Goal_Number]? Or is there an other way to do this?

Can the distance between reached number and target number, if target number is not reached yet, be colored?

Thanks

R.W.

[v-lili6-msft · Accepted solution]

hi, @Anonymous

     After my research, the best practice is that: 

using Sum of Validation_Numbers[Validation_Number] as value

using Sum of Goal_Numbers[Goal_Number] as Target value

add a measure by all() like this

max = CALCULATE(SUM(Goal_Values[Goal_Number]),ALL(Goal_Values))

as Maximum Value

I'm afraid it couldn't achieve in Power BI for now that if the target number is not reached yet,  the distance between reached number and target number couldn't be colored.

For your requirement, you could post your new idea in Power BI ideas and make this feature coming sooner.

Best Regards,

Lin

[Chihiro]

Not the default visual.

May be try Dial Gauge or Tachometer custom visual.

https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381184

https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104380937?tab=Overview