Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
san_jois
Resolver I
Resolver I

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

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

View solution in original post

5 REPLIES 5
punnas1
Helper II
Helper II

@v-ljerr-msft , In this senario, grand totals showing wrong numbers

v-ljerr-msft
Employee
Employee

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

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,

 

 

 

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.