Planned vs Actual Percentage by Date and Product

[Rambo92]

Hi All,

I got two tables where Table 1 is Actual Table what achived vs Table 2 which is planned. I have imported both the tables in Power BI and trying to find the percentage of achieved. I am getting the right numbers if no filters are applied, but not working if i keep the date or ID filters. I basically need, count of achived by count of planned in percentage and if any filters applied as well like date and id.

Here is the sample data if needed. Here i kept only 1 date for reference. But in actual data, we got many dates along with different categories of products and other variables aswell.

Thanks in advance.

[lbendlin · Accepted solution]

Create a data model that has dimension tables for the items you want to filter on (ID, Product, Date)  and wire them accordingly to your two independent fact tables. Then you can create measures that calculate the percentages according to your selected filters.

[Rambo92]

Correct me if i am wrong, are you telling me to create 3 different tables for id, product and dates and manage in relationships tab ?

[lbendlin]

correct, that is what I am suggesting.  Having a data model that fits your business scenario is always the preferred option.

[grantsamborn]

Hi @Rambo92 

Can you explain how you are calculating Percentage?  (Since there are no numeric fields, I'm guessing it is a row count for distinct combinations or something like that.)

Also, can you show your expected result based on the sample data that you provided.

Thanks

[Rambo92]

I am calculating by count of rows in both tables, for example in the sample data. If we filter id 1, then it should show 3(actual)/5(planned) which should show 60% achieved on id1. 

I need to create a measure which should show based on date and id filters. 

In the sample data, there is only 1 date but it has many dates in the original dataset.