cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Prasad84 Frequent Visitor
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
Super Contributor

Re: Looking for discount corresponding to maximum sales

Hi @Prasad84

 

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

 

Max sale op.PNG

 

 

 

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
Super Contributor

Re: Looking for discount corresponding to maximum sales

Hi @Prasad84

 

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

 

Max sale op.PNG

 

 

 

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
Community Support Team

Re: Looking for discount corresponding to maximum sales

@Prasad84,

 

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.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 133 members 1,601 guests
Please welcome our newest community members: