Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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,
Solved! Go to 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.
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
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
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,
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
User | Count |
---|---|
102 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |