Cleanest way to get show goals achivements

[PabloQuinP]

Hi community!

I need to show the goals achievement of my company and I'm not sure at all of the best way to do it, so letss see if you can help me.

I have this data structure

Dealers_table(dimensional): With the unique dealers id, name, etc.

Sales_table(fact): With all the historical sales records.

Objetives_table(fact): With the historical monthly objectives.

If it was SQL what I would like to get is essencially:

Select distinct Dealer, date(mm-yyyy), Objective, count(Sale_id)

FROM Dealers

LEFT JOIN Sales_table on sale.dealer_id = dealer.id

LEFT JOIN Objectives_table on obj.dealer_id = dealer.id

How could I get in the clenest way? 

I'm not familiar with table functions so I hope you can help me. Should I use calculate functions or create a table? Maybe try to get it in power query? Im concern about performance and best practise becuse I'm kinda new with this tool.

My current aproach is Calculates functions, however I feel like it has some drowbacks. For example, when I plot a table (Dealer, count of sales and objectives) Power Bi asign the objective vale to every single sale row befure make the count so I get N times the objective value like:

Row 1, Dealer_1, Sale id, objective

Row 1, Dealer_1, Sale id, objective

As a result the table summarize it like:

Dealer | Count | Objective

Dealer_1 | 2 | 2 x Objective

This is unaccurate ofc and create other issues like in totals row, etc.

How would you solve this in the cleanest way?

[Anonymous · Accepted solution]

Hi @PabloQuinP ,

Please have a try.

One way to solve this in a clean way would be to create a measure that calculates the count of sales and a separate measure that calculates the objective for each dealer and month. You can then use these measures in a table visual to display the data.

Sales Count = COUNTROWS(Sales_table)

Objective = SUM(Objectives_table[Objective])

Achievement % = DIVIDE([Sales Count], [Objective])

You can then add this measure to the values section of the table visual to show the achievement percentage for each dealer and month.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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