Reply
Regular Visitor
Posts: 19
Registered: ‎08-07-2018
Accepted Solution

Gauge cumulative values and targets

Hi,

I have imported two tables : Validations and Goal_Values.

The table Validations contains following columns:

 

Gauge_01.jpg

 

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:

 

Gauge_02.jpg

 

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:

 

Gauge_03.jpg

 

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]

 

Gauge_04.jpg

 

In the Visualisations I have 2 slicers:

  • List with Field Goald_Numbers[Goal_Year]

Gauge_07.jpg

 

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

 

Gauge_08.jpg

 

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?

 

Gauge_09.jpg

 

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

 

Gauge_10.jpg

 

Thanks

R.W.


Accepted Solutions
Community Support Team
Posts: 1,393
Registered: ‎07-30-2018

Re: Gauge cumulative values and targets

hi, @RudyWelvaert

     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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post


All Replies
Established Member
Posts: 179
Registered: ‎06-13-2017

Re: Gauge cumulative values and targets

Community Support Team
Posts: 1,393
Registered: ‎07-30-2018

Re: Gauge cumulative values and targets

hi, @RudyWelvaert

     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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.