Forum Discussion
Assistance required with formulating Measures
New to Power BI and working with DAX Formulas. I'm trying to work with three formulas. The first would provide me with the customer that purchased the greatest amount of a part number during a spec...
- Hi again, I belive your problem is that you are not using a Customer table that is related to your Sales_Analysis table like I am, and when you put that into the TOPN function it will just contain all the rows of that table. Hence, if you more than one row with the same customer name it will be duplicated. If you have a customer table then you should use that instead, or you could try to replace 'Sales_Analysis' with VALUES('Sales_Analysis'[Customer]) in the TOPN function. Best regards, Kristjan
Hi there,
Maybe this will solve your problem, you will have to change the names of the columns and the measures. This measure will show the top 1 customer that bought the most in the current context. Hence, if you will put the part number in the matrix it should show the top 1 customer for each part, if the model is correctly connected. This will apply to the dates as well, i.e. top 1 customer for the dates in current context, you can override the context with a CALCULATE in the TOPN function, to filter on this year or the last.
Top 1 cust =
PATHITEM(
CONCATENATEX(
TOPN(
3;
'Sales Customers';
[$ sales]
);
'Sales Customers'[CustomerName];
"|";
[$ sales];DESC
);
1 // Just changed this to 2 and 3 for the other measures
)
If this solves your problem you please LIKE and accept this as solution.
Best regards,
Kristjan76
Kristjan76--thank you for the code that has gotten me close to fixing the issue. I believe that your code will be the final solution once I see why it shows duplicate customers in some instances. It should be showing Company "A", Company "F" and Company "L" but shows Company "A", Company "A" and Company "F". I have added another matrix with a specific part number and the sales information by customer to validate what it is showing and it should not be showing Company "A" twice. Please do respond to this comment and once you respond I will mark your comment as the solution so you get credit off of your last response. Again, thank you for your assistance with this. The code that was modified from your original solution is shown below. If you think you may know what the issue could be, please let me know. Top 1 cust = PATHITEM( CONCATENATEX( TOPN( 3,'Sales_Analysis',[Sales Total] ), 'Sales_Analysis'[Customer], "|", [Sales Total],DESC ), 1 // Just change the value of "1" to a "2" for the second measure and "3" for the third measure. )
- Top 1 cust = PATHITEM( CONCATENATEX( TOPN( 3,'Sales_Analysis',[Sales Total] ), 'Sales_Analysis'[Customer], "|", [Sales Total],DESC ), 1 // Just change the value of "1" to a "2" for the second measure and "3" for the third measure. )
- Hi again, I belive your problem is that you are not using a Customer table that is related to your Sales_Analysis table like I am, and when you put that into the TOPN function it will just contain all the rows of that table. Hence, if you more than one row with the same customer name it will be duplicated. If you have a customer table then you should use that instead, or you could try to replace 'Sales_Analysis' with VALUES('Sales_Analysis'[Customer]) in the TOPN function. Best regards, Kristjan