TOPN and segments

[Audrey_ADAPEI35]

Hello Everyone,

I am looking for a solution after struggling for a day on this measure.

I have a data set where i have my sales by client, by firms, by activity, all of that with date.

I want to have a measure that will give me the total amount of sales of my top 10 client and that will change depending of the choice of the user on the segements (he can choose the firm, the activity, the year and the month).

Up now, I have made a table that summarize my data like this (example) :

the name of my summariez table is "BDD GESSI"

Client	Sales amount	Firms	activity	year
id1	20 €	Macdonald	hamburger	2023
id1	40 €	Macdonald	coke	2023
id4	100 €	Burger king	hamburger	2022

Then i have made a measure :

TOP10 CA = SUMX(TOPN(10,'BDD GESSI - SUMMARIZE',[Sales amount (GESSI - summarize)],DESC),[Sales amount (GESSI - summarize)])

It's working only if it selects all month of the year ; if he choose only january's data, the measure don't change. (because it's not a column of my summarize table).

If I put in my summarize table the date (instead of the year), i don't have anymore agrgregated sales by client...

What can i do to make it work? I have been looking for many youtube videos to help but their exercises are too "simple".

Thank you for your response.

[Audrey_ADAPEI35]

Hi user 123abc,

 Thank you very much for replying ! It somehows works ! But I still have a problem and it's the data base.

 First, I changed the formula to (I deleted the part "ALL('BDD GESSI'), bc it didn't work, my value was somehow multiplicated by a lot) :

TOP10 CA (2) = SUMX(

TOPN(10, 'BDD GESSI - SUMMARIZE', [CA (GESSI - summarize)], DESC),

    CALCULATE(

        [CA (GESSI - summarize)],

VALUES('BDD GESSI - SUMMARIZE'[Dossier]),

VALUES('BDD GESSI - SUMMARIZE'[Atelier VF]),

VALUES('BDD GESSI - SUMMARIZE'[Date fact])

)

Then, I am not sure what to do with the database.

The database I use is created by the function SUMMARIZE. My first database is called « BDD GESSI » and the summarized one is called « BDD GESSI – summarize ».

BDD GESSI have as many lines as client, products details, firm, date, invoices… It’s quite detailed.

BDD GESSI-summarize looks like this

Do i have to make links in the model view for my summarized tables ?

Because if I don’t, my data can’t works with the segment.

Also, for the links, I have to make a link between my date base and the date of ‘bdd gessi – summarize’.  It makes me be able to deploy my data by year, by month…

(If I make a link between months or year, my segments year / months doesn’t work)

But this cause me problems because it will make the top 10 of my sales amount based on the date...

On this example, my total sales amount is not working bc it takes in count the date of invoice :

My measure will sum the orange values where if I make a matrix giving the top 10 client, it takes in count the blue value.

 5 851 + 27 € = 5 878 €

I am pretty sure I am not far from resolving this !

Thank you for your return (and sorry for my english if it’s not perfect)

[123abc]

It seems like you're facing a challenge in creating a measure that calculates the total sales for the top 10 clients based on user-selected segments, including firm, activity, year, and month. The issue is that your current measure doesn't respond to changes in the selected month.

To address this, you can modify your measure to include filters based on user selections. Assuming you have a date column in your "BDD GESSI" table, you can adjust your measure like this:

TOP10 CA =
SUMX(
TOPN(10, 'BDD GESSI', [Sales amount (GESSI - summarize)], DESC),
CALCULATE(
[Sales amount (GESSI - summarize)],
ALL('BDD GESSI'),
VALUES('BDD GESSI'[Firms]),
VALUES('BDD GESSI'[activity]),
VALUES('BDD GESSI'[year]),
VALUES('BDD GESSI'[month]) // Add this line to consider the selected month
)
)

In this modified measure, I've used the CALCULATE function with the ALL function to remove any existing filters on the 'BDD GESSI' table. Then, I've added filters based on the selected firm, activity, year, and month using the VALUES function for each corresponding column.

This should enable your measure to dynamically adjust based on the user's selections for the month. If your date column contains both year and month information, you can adapt the code accordingly.

Remember to replace 'Firms', 'activity', 'year', and 'month' with the actual column names from your "BDD GESSI" table.

Try implementing these changes and see if it resolves your issue. If you encounter any further challenges or if your data model has specific complexities, feel free to provide more details for additional assistance.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.