cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chronis69 Member
Member

Sales KPIs

Hi

 

I have the following challenge. My sales table looks like the following:

 

<timestamp>, <reseller>, <customer>, <sales_value>

 

I want to be able to record the following KPIs for each month

 

1. Total monthly sales

2. Total monthly sales from new resellers (new is a reseller how has placed his first order the month in question)

3. Total monthly sales from new customers (new is a reseller how has placed his first order the month in question) 

4. pct of new_reseller_sales/total_sales (in this month)

5. pct of new_customer_sales/total_sales (in this month)

 

I'm sure that this is very easy to do in DAX and it would take me about 15mins to hack it in XLS but im in the early stages of the DAX learning curve.

 

Thank you in advance

 

Chronis

 

1 ACCEPTED SOLUTION

Accepted Solutions
chronis69 Member
Member

Re: Sales KPIs

@v-frfei-msft hi

 

I've had a discussion with a colleague over the weekend and he has helped me produce a solution.

 

Basically, for every order record we have created a new column "new partner sale" and a new column "new customer sale" that would take 1 if the said order is from a partner/customer who has put his first order the same month of the order in question (i.e the min timestamp for the particular partner/customer is the same of the order in question)

 

this gave me something to count in terms of new orders. doing a SUMX with the sales amount muliplied with the the value of these I created a measure that contains only the new sales of any one month.

 

QED... works perfectly. 

 

My new column looks something like this

 

_new customer order =
-- returns 1 if the current order is at the first month the end-customer EVER placed an order, 0 otherwise
-- we try to count the orders that are repeat sales from an end-customer vs first time orders (in the context of a month)
VAR current_customer = O365LicenseReport[Customer Company Name]
VAR current_order_month = YEAR(O365LicenseReport[OrderCreationDate])+12*MONTH(O365LicenseReport[OrderCreationDate])
VAR min_customer_order_date =
CALCULATE(MIN(O365LicenseReport[OrderCreationDate]);
FILTER (ALL(O365LicenseReport);O365LicenseReport[Customer Company Name] = current_customer && O365LicenseReport[Vendor]="Microsoft")
)

VAR min_order_month = YEAR(min_customer_order_date)+12*MONTH(min_customer_order_date)
VAR new_order = if(current_order_month=min_order_month;1;0)
RETURN new_order

 

then the measure is the following

 

Licenses new customer = sumx(O365LicenseReport;O365LicenseReport[Licenses]*O365LicenseReport[_new customer order])

 

 

 

2 REPLIES 2
Community Support Team
Community Support Team

Re: Sales KPIs

Hi @chronis69,

 

Could you please share your sample data and excepted result to me? You can upload your file to dropbox and share the link here.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
chronis69 Member
Member

Re: Sales KPIs

@v-frfei-msft hi

 

I've had a discussion with a colleague over the weekend and he has helped me produce a solution.

 

Basically, for every order record we have created a new column "new partner sale" and a new column "new customer sale" that would take 1 if the said order is from a partner/customer who has put his first order the same month of the order in question (i.e the min timestamp for the particular partner/customer is the same of the order in question)

 

this gave me something to count in terms of new orders. doing a SUMX with the sales amount muliplied with the the value of these I created a measure that contains only the new sales of any one month.

 

QED... works perfectly. 

 

My new column looks something like this

 

_new customer order =
-- returns 1 if the current order is at the first month the end-customer EVER placed an order, 0 otherwise
-- we try to count the orders that are repeat sales from an end-customer vs first time orders (in the context of a month)
VAR current_customer = O365LicenseReport[Customer Company Name]
VAR current_order_month = YEAR(O365LicenseReport[OrderCreationDate])+12*MONTH(O365LicenseReport[OrderCreationDate])
VAR min_customer_order_date =
CALCULATE(MIN(O365LicenseReport[OrderCreationDate]);
FILTER (ALL(O365LicenseReport);O365LicenseReport[Customer Company Name] = current_customer && O365LicenseReport[Vendor]="Microsoft")
)

VAR min_order_month = YEAR(min_customer_order_date)+12*MONTH(min_customer_order_date)
VAR new_order = if(current_order_month=min_order_month;1;0)
RETURN new_order

 

then the measure is the following

 

Licenses new customer = sumx(O365LicenseReport;O365LicenseReport[Licenses]*O365LicenseReport[_new customer order])

 

 

 

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 85 members 1,545 guests
Please welcome our newest community members: