cancel
Showing results for
Did you mean:
Prasad84 Frequent Visitor

Looking for discount corresponding to maximum sales

Hi,

I have a situation where I need to show what is the discount on which maximum sales happened for a geography key.

For e.g

There could be two geographies A and B having 2 sub areas 1 and 2. So there will be 4 geographic keys. A1,A2,B1,B2.

For each key there will be multiple invoices at various discounts,

My problem is to show in a single tabular report, what is total sales for each geography and what is the discount at which highest sales is happening.

For eg.

Suppose for A1 I have as below:

Geography  Sub region  Discount  Sales

A                   1                  30%        \$100

A                   1                   40%        \$80

The output report should  be like

Geography   Subregion     Total Sales     Discount at which highest sales is

A                     1                     \$180             30%

Can you pls guide.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
rajendran Super Contributor

Re: Looking for discount corresponding to maximum sales

Use the below formula to create a new column for total sales:

Total_Sales = CALCULATE(SUM(Sales[Sales]),FILTER(Sales,Sales[Geography]=EARLIER(Sales[Geography]) && Sales[Subregion]=EARLIER(Sales[Subregion])))

To get the discount , corresponding to the max sales, create a flag column to mark the row which has max sales for each geo/ subregion

Max_sales = IF( CALCULATE(MAX(Sales[Sales]),FILTER(Sales,Sales[Geography]=EARLIER(Sales[Geography])&& Sales[Subregion]=EARLIER(Sales[Subregion])))=Sales[Sales],1,0)

and then display the discount where the flag is 1.

max_discount = SWITCH(TRUE(),Sales[Max_sales]=1,Sales[Discount])

To display only the records where max sales happens, filter using Max_sales=1 Hope this helps.

P.S: To get the second table, pls select do not summarize option of the newly created columns. ( Go to visualizations-> Fields tab -> Values section-> right click the colun-> select do not summarize)

Thanks

Raj

2 REPLIES 2
Highlighted
rajendran Super Contributor

Re: Looking for discount corresponding to maximum sales

Use the below formula to create a new column for total sales:

Total_Sales = CALCULATE(SUM(Sales[Sales]),FILTER(Sales,Sales[Geography]=EARLIER(Sales[Geography]) && Sales[Subregion]=EARLIER(Sales[Subregion])))

To get the discount , corresponding to the max sales, create a flag column to mark the row which has max sales for each geo/ subregion

Max_sales = IF( CALCULATE(MAX(Sales[Sales]),FILTER(Sales,Sales[Geography]=EARLIER(Sales[Geography])&& Sales[Subregion]=EARLIER(Sales[Subregion])))=Sales[Sales],1,0)

and then display the discount where the flag is 1.

max_discount = SWITCH(TRUE(),Sales[Max_sales]=1,Sales[Discount])

To display only the records where max sales happens, filter using Max_sales=1 Hope this helps.

P.S: To get the second table, pls select do not summarize option of the newly created columns. ( Go to visualizations-> Fields tab -> Values section-> right click the colun-> select do not summarize)

Thanks

Raj Community Support Team

Re: Looking for discount corresponding to maximum sales

You may also use measure below.

Measure =
MAXX ( TOPN ( 1, Table1, Table1[Sales], DESC ), Table1[Discount] )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements Top Kudoed Authors
Users Online
Currently online: 133 members 1,601 guests
Recent signins:
• Renjith • khush19 • caesaralpha • puttaswamyrps • Bphilton5 • st586 • Nikoko • Kumaran • jtkeithfamly1 • Lina_111227 • DeeFox • GokhanVarol • Bino • kyliecoast 