Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Rambo92
Helper I
Helper I

Planned vs Actual Percentage by Date and Product

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.

Rambo92_0-1678806543242.png

Thanks in advance.

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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.

View solution in original post

5 REPLIES 5
lbendlin
Super User
Super User

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.

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 ?

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

grantsamborn
Solution Sage
Solution Sage

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

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors