cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
san_jois Regular Visitor
Regular Visitor

Compare with previos year sales quantity and segregate into Positive and negative performers

Dear all experienced PBI Desktop users,

Greetings,

I have a Sales Quantity flowing column in which daily data gets added.

I have slicers for Year and month.

As per the selected slicer, sales quantity should be compared with Sameperiodlastyear and the difference (positive, zero or negative categories) should be segregated so that the count of customers in each category and the corresponding sales quantity sum should be availabe.

Created measures of Total sales and Total sales Last year help me upto the level of populating a table in whcih I get the differences mentioned as the categories stated above.

However, to count the no. of customers falling in each category is where I am facing problem.

The moment I try with a new measure as Positive count=Calculate (Count(partycodes),((Total sales)-(Total sales Last year}}>=0), it considers the overall table values and not individual rows.

So I am trying to create a calculated column..

But am presently stuck with 'circulatory reference' error..

 

Kindly help

 

Thanks in advance

 

Regards,

1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

Re: Compare with previos year sales quantity and segregate into Positive and negative performers

Hi @san_jois,

 

If I understand you correctly, you should be able to use the formulas below to create the measures in your scenario. Smiley Happy

 

1. Add a Customer dim table which contains unique [Name of Customer], if you don't have yet.

Customers = DISTINCT ( 'Table1'[Name of Customer] )

2. Then create the measures.

Measure1 =
SUMX ( 'Customers', IF ( [Monthly Sales Diff] < 0, 1, 0 ) )
Measure2 =
SUMX ( 'Customers', IF ( [Yearly Sales diff] < 0, 1, 0 ) )

 

Regards

4 REPLIES 4
v-ljerr-msft Super Contributor
Super Contributor

Re: Compare with previos year sales quantity and segregate into Positive and negative performers

Hi @san_jois,

 

Could you post your table structures(including the measures you're using) with some sample/mock data, and the expected result against the data? So that we can better assist on this issue. It's better that you could also share a sample pbix file . You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. Smiley Happy

 

Regards

san_jois Regular Visitor
Regular Visitor

Re: Compare with previos year sales quantity and segregate into Positive and negative performers

Hi,

In data, I have a table consisting:

Date, Name of Customer, Ordered quantity

 

Measures I have created are:

Quantity (meaning Sales this year)

Total sales last month (meaning Sales last month.  have used DATEADD(), function)

Monthly Sales Diff (meaning the difference in sale compared to last month)

Total sales LY (meaning Sales last year. I have used SAMEPERIODLASTYEAR() DAX function)

Yearly Sales diff (meaning the difference in sales compared to last year)

 

With this, I am able create a table in the PBI canvas mode and call for 'Customer' and 'Difference Year-on-Year' measures to be column headers in the table

 

Till here it works fine.

 

Sample table created using Measures

 

I want to count the no. values which are less than Zero under the columns Monthly sales difference and Yearly sales difference

 

Should I add calculated columns (which I am a bit hesitent because of large data and associated increase in RAM)?

 

Or is there a way out using measures?

 

Thanks,

 

 

 

v-ljerr-msft Super Contributor
Super Contributor

Re: Compare with previos year sales quantity and segregate into Positive and negative performers

Hi @san_jois,

 

If I understand you correctly, you should be able to use the formulas below to create the measures in your scenario. Smiley Happy

 

1. Add a Customer dim table which contains unique [Name of Customer], if you don't have yet.

Customers = DISTINCT ( 'Table1'[Name of Customer] )

2. Then create the measures.

Measure1 =
SUMX ( 'Customers', IF ( [Monthly Sales Diff] < 0, 1, 0 ) )
Measure2 =
SUMX ( 'Customers', IF ( [Yearly Sales diff] < 0, 1, 0 ) )

 

Regards

san_jois Regular Visitor
Regular Visitor

Re: Compare with previos year sales quantity and segregate into Positive and negative performers

Thank you v-ljerr-msft It works fine. Thanks again for introducing me to the SUMX function. I am curious now to explore more such functions.. Regards,

Helpful resources

Announcements
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.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 259 members 2,940 guests
Please welcome our newest community members: