Combine real and planned data in PowerBI KPI visualizations

Question

I've got two primary datasets:

Real data with all sales transactions.

For example RealData:

Date;Sales
16-01-2017;1200
20-01-2017;1500
05-02-2017;800
08-02-2017;1100
13-02-2017;300

Etc.

A plan with total sales I want to achieve, with totals at the last day of each month.

For example EndOfMonthTargets:
```
Date;Sales
31-01-2017;3000
28-02-2017;2500
```
Etc.

In my real data I (a) also have data from the years before 2017, and obviously I have the targets for each month in 2017. The RealData is always live / up to date.

What I'm trying to do is show KPI visualizations: how is the real data vs the plan doing. However, I can't figure out how to relate the two datasets, because the cannot really be joined together.

What is the idiomatic way to do this in PowerBI?

I've tried:

adding a column to each source with its respective status ("real" vs "plan")
creating a union (i.e. "Append Queries as New")
adding a column Month = MONTH([Date])

Giving me this data:

But that's weird, because then the KPI visualization will include the "Plan" data either at the start or worse at the end where it will be plotted as the "current" number.

The expected output is a KPI visualization:

showing the absolute number of sales summed by year (or month, if my slicers are set that way!)
with the Target goals retrieved from the plan data
with a trend based on previous years (not included in my sample data)

How do I achieve that?

It unclear to me, do you want to compare january 2017 total sales from real data with 31-01-2017 - 3000?. It would be useful if you include expected result. — alejandro zuleta
@alejandrozuleta Your assumption is correct. I've tried to included the expected result in my last paragraph, will try to enhance my question a bit. But basically, yeah: if my slicer is set to "January" then I want my KPI to show that the actual number is 2700, and make it red showing it's -300 relative to the Plan to have 3000 at the end of January. — Jeroen

Leonard Leonard · Accepted Answer · 2017-03-08T18:39:34

There are a few ways to join tables like this. What you have are 2 tables with different granularities: a sales table that goes to the day, and a target table that goes down to the month. It's a common request to model them against each other, and it boils down to having a lookup table that both fact tables can join with.

What I would recommend:

Have a table of your real data
Have a table of your plan/target data, using the last day of the month is fine
Do not relate these 2 fact tables together directly
Rather, have a separate date lookup table, to which both tables are joined. This date dimension should, at a minimum, have a month & year column (e.g. March 2017).
Join both fact tables to your date table
Hide the date fields in your 2 fact tables, so that you aren't tempted to use them in visuals (you have to use the date fields from the lookup table instead)

You can then create measures to SUM your actual and targets. And then additional measures that subtract those 2 measures against each other (or divide one into the other). All those measures will then be sliceable by the month & year on your date table. (You could slice them by date too, but because targets are assigned to the last day of the month, that granularity will be less than helpful.)

There's a lot of good information & examples here: http://www.daxpatterns.com/handling-different-granularities/

Combine real and planned data in PowerBI KPI visualizations

4 Answers