cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 6 members 3,205 guests
Please welcome our newest community members: