cancel
Showing results for
Did you mean:
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

Regards,

1 ACCEPTED SOLUTION

Accepted Solutions
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.

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

Regards

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.

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,

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.

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

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,

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 259 members 2,940 guests
Recent signins: