Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
1 year ago

Compare Records

As I can do the following, I'm working in Power BI with 2 tables that come from SQL queries, the tables are called Query1 and Sales Cube. both tables are for the sale of products, they have the following fields, Seller, SellerCode, Description (Product), Grouped Customer (customer), SaleBs, SellerType and Sales Classification by Customer (column calculated to classify each record as if it was a sale or not according to the SaleBs field, if it is greater than 0 it is classified as 'With Sale' otherwise 'No Sale'). The difference between both tables is that Query1 only gives me data from February 2025, it is a table that is generated from a query that receives a parameter with the month that the information is required, so it does not have a field that refers to the date, month or year. On the other hand, Sales Cube is a table that has, so to speak, a history of sales, it has the same fields as Query1 but this one also has month, year and day fields (important because I am only interested in the records of the year 2024). I also have a third table called SellersTable that has the sellers' data, its fields are Name, Code, Type and Mail. Now what I need to do is make a comparison between all the customers (Grouped Customer) I had in 2024 (Sales Cube) and those I have registered today (Query1). what I need is to form a matrix that has Seller, Classification of customers (2025) if they had sales or not comparing with the total number of customers in 2024, in values I want to have the number of customers 2024, number of customers 2025 and a measure to obtain the coverage that was had based on customers in 2024 and so far in 2025.
I've already achieved something but it's not quite right:

The problem is that when I display the level of customers with or without sales, I get completely all the customers in 2025 without distinguishing or filtering by only the records that that specific seller had in 2024.

At the end of it all, I need to have the list of all the customers that each salesperson had in 2024, next to a column that shows an equal count of those branches in 2024, which already have and do not have sales in 2025, to have something like this:

A matrix that in the rows has Salesperson, sales classification, and customers (that when I display the level of With or No sales I can have the total number of customers that were had in 2024 and next to it is marked with a 1 as when counting the customers that were already served in 2025 ), while in the values it has the customers of 2024, the customers of 2025, the sum of the sales of those customers of 2025 and the percentage of customer coverage.



1 Reply