Target Setting - in Data Model

[Ikay]

we are moving from static php reporting dashboard in the fire service where data is only presented in tables pulled from SQL server.

I have transformed this into a power bi dashboard with more interactions. so where as for example, the primary and secondary fires were 2 separate tables, now, its a filter.

The challenge now, is how to incorporate the Targets. Targets are held in an excel file by month and financial year FOR each of the tables - so for example primary fires has its own target, by month, by year.

The challenge now is how to structure this target spreadsheet, in a way that will work with my model. #general-power-bi

My initial thought is to manually create this as a measure for each one for each year (500 measures across 2 years)

Please see sample table structure attached. Any help will be greatly appreciated.
Sample tables.xlsx (11.4 KB)

Thank you

[Hemantsingh]

Hi @Ikay

I went through your excel file. I felt it is really difficult to map your excel file sheets to what you have explained in your post. I request you if you can share a small sample pbix file with some sample data that mimic what actually you have done so far and a scenario which you want to achieve.

Regards,
Hemant

[Ikay]

dashboard sample.pbix (717.2 KB) Thank you so much Hemant for the quick reply. I have attached a sample pbix file. This includes the target table as is.

also attached is the old way of reporting with the target highlighted.

The challenge is to find a way to incorporate the targets into my dashboard.

Thank you

[Hemantsingh]

Hi @Ikay,

After looking at your model,first thing i can say is that your target setting table should be long instead of wide. In other words all the months column should be in one column and they should follow a date format not a name and then connect that column to your date table. This way for a selected month on the date slicer you will be able to get calculation from target table.

Lemme know whether this input helps. Will discuss further if it didn’t work out the way you want it to be.

Regards,
Hemant

[Ikay]

Thank you Hemant for looking at this.

I tried this before. the thing is each row on the target sheet relates to a target say for Primary fires. so if even I moved the months as a column, I still need to figure out a way to relate each row to the corresponding target.

Does this make sense.

Regards

[Greg]

Hi @Ikay.

From what I could see from your sample PBIX, the target data obtained from your spreadsheet contains a linear distribution of [Year Total] into the 12 broken down into the component months for targets.

You don’t need the monthly breakdown for [Target]; you can use a simple division to get 1/12 of the [Year Total] to apply to each month

• 72/183, or about 40% of the [Target] records have a [Total] equal to the sum of the component months
• the remaining 111/183, or about 60% appear to have the same value for [Year Total] as for each month; I think these should be re-evaluated

For “Last Year”, the values are different for each month.

You’ll need some sort of mapping to link the [Measure ID] to your current year results.

I don’t see a need for anywhere near 500 measures; I’d expect you can use the context in the matrix and a single 3-section measure (section 1-Target, section 2-Last Year, section 3-Current Year).

Apologies for talking in circles a bit; I expect it because I got stuck in your data model and was “digging-myself-deeper”. I’d spend some time revising your data model into a more classic “waterfall” design before tackling the measure.

Hope this helps.
Greg

[EnterpriseDNA]

Hi @Ikay, did the response provided by the contributors help you solve your query? If not, how far did you get, and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

[Ikay]

Thank you Greg for taking time to review this.

2 key challenges I have here is that each row on the target sheet represents a different “measure” like target for Primary fire, target for secondary fire. etc-- about 500 of these.

2ndly they would like the flexibility of making the targets change by month.

Thanks alot

[Greg]

Hi @Ikay. Here’s an example using a spreadsheet similar to yours and a single measure. I extracted the [Type] from the matrix and only needed a single section in the measure.

Matrix Value = 
VAR _CurrentMeasureID = SELECTEDVALUE( 'Fire Measures'[Measure ID] )
VAR _CurrentType = SELECTEDVALUE( Types[Type] )
VAR _CurrentMonth = SELECTEDVALUE( Dates[MonthOfYear] )
VAR _FireRecord = FILTER( Fires, 
                    AND(
                        Fires[Measure ID] = _CurrentMeasureID,
                        Fires[Type] = _CurrentType
                        ) 
                    )
VAR _Result = CALCULATE(
    SWITCH( TRUE(),
        _CurrentMonth = 1, MAX( Fires[Jan] ),
        _CurrentMonth = 2, MAX( Fires[Feb] ),
        _CurrentMonth = 3, MAX( Fires[Mar] ),
        _CurrentMonth = 4, MAX( Fires[Apr] ),
        _CurrentMonth = 5, MAX( Fires[May] ),
        _CurrentMonth = 6, MAX( Fires[Jun] ),
        _CurrentMonth = 7, MAX( Fires[Jul] ),
        _CurrentMonth = 8, MAX( Fires[Aug] ),
        _CurrentMonth = 9, MAX( Fires[Sep] ),
        _CurrentMonth = 10, MAX( Fires[Oct] ),
        _CurrentMonth = 11, MAX( Fires[Nov] ),
        _CurrentMonth = 12, MAX( Fires[Dec] ),
        MAX( Fires[Total] )
    ),
    _FireRecord
)

RETURN
_Result

I’m sure there are many ways to do this; this is just one.

Hope this helps.
Greg
eDNA Forum - Fires.pbix (223.6 KB)
Fires.xlsx (10.3 KB)

[Ikay]

Thank you so much Greg!.

This is way over my head right now. I will try to figure out a way around it.

Thank you again

[Ikay]

Thank you all!.. I figured a way to do it by firstly arranging the months as rows, and linking to the date table.

This way, It would only work as a KPI card, and with the dates slicer. of course with each card, I have to filter to the said measure.

THanks

[Hemantsingh]

Hi @Ikay

Is this solution different from what i have suggested?? If yes than can you please paste a screenshot of the new transformed table in which you have placed month on rows.

Regards
Hemant

[Ikay]

Hi Hemant,
Apologies, it is actually what you suggested. Thank you so much

[Hemantsingh]

Can you please accept mine suggested solution as a solution to your problem too…it will help others too.

Regards,
Hemant