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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 specific year.  I would like the second measure to filter out the results of the firs measure when selecting the customer that purchased the largest amount.  I would like the third measue to filter out the first and second measures' results.  This way, the matrix would show the top three customers to purchase a specific item.  I have multiple part numbers listed in a matrix and the desired result is that it will show the top two for each part number without having to drill down into one specific part number for it to show.

 

NOTE:  Measure #1 and #2 does not "Error out" and show the caution sign beside the metric.  Only Measure #3 shows this.  Measure #1 and #2 is shown as a monetary amount where I am trying to have them display the customer.  Measure #2 also displays the same value as #1 so that is another issue.  Any sugguestions on fixing what I have started with or suggestions on going a different direction?  Code examples would be appreciated at this point!  Thanks in advance for helping!

 

MEASURE #1

TOP3 Test =
VAR RankingContext=
VALUES('Sales_Analysis'[Customer])
VAR TopNumber=3
RETURN
CALCULATE(
Sales_Analysis[Sales Total],
TOPN(TopNumber, ALL(Forecast[Part Number]), Sales_Analysis[Sales Total]),
RankingContext
)

 

MEASURE #2

2ndTOP3 Test =
VAR RankingContext=
VALUES('Sales_Analysis'[Customer])
VAR TopNumber= 3
RETURN
CALCULATE(
Sales_Analysis[Sales Total],
TOPN(TopNumber, ALL(Forecast[Part Number]),
RankingContext
))
 
MEASURE #3
3rdTOP3 Test =
VAR RankingContext=
VALUES('Sales_Analysis'[Customer])
VAR TopNumber=3
VAR ExcludeNumberOne=
[TOP3 Test]
RETURN
CALCULATE(
Sales_Analysis[Sales Total],
TOPN(Topnumber, ALL(Forecast[Part Number]),Sales_Analysis[Sales Total]),
RankingContext <> ExcludeNumberOne
)
**** MEASURE #3 ERROR MESSAGE STATES---The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column.
1 ACCEPTED SOLUTION

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

View solution in original post

8 REPLIES 8
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @Anonymous,

 

Have you solved your problem?

 

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

 

If you still need help, please share some data sample and your desired output so that we could help further on it.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thank you for the reminder to this point. I will be replying to the proposed solution in the next hour or so. I was testing it yesterday trying to look at it where the values returned did not match. I will provide details with the full reply.
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @Anonymous,

 

Have you solved your problem?

 

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

 

If you still need help, please share some data sample and your desired output so that we could help further on it.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Kristjan76
Responsive Resident
Responsive Resident

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

Anonymous
Not applicable

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. )
Anonymous
Not applicable

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
Anonymous
Not applicable

First off, many thanks to you for take ng the time to assist me with my questions and provide a possible code correction. I adjusted the proposed formula just a bit and it is pulling the data but is duplicating some of the return values. I would have replied sooner but was testing it out late yesterday evening and have been delayed with other work this morning. I will reply within the hour with further details and the code adaptation.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.