I am working with two Matrix tables based on the same Sales data - one is a Sales by Month and the second table is Cumulative Sales by Month. Rows are by Sales Rep. All our Reps are showing in the Sales by Month table, but for some reason the Cumulative Sales table drops one of our Reps - Frank V. Both tables are using the same field for the Row information. I can’t figure out why this Rep is being dropped from the second table. Uploading screenshot and
PBIX - issue is on the Monthly Sales tab.Zoho Sales v7_eDNA.zip (5.6 MB)
1 Like
Hi @kjssdca. Frank V is the only one with zeros for both months in 2021 … I expect that’s the difference that is causing your issue. Perhaps if a different filter than “Monthly Cum = 0” is used then he will appear in the second matrix.
Greg
1 Like
I did some more troubleshooting on the missing Sales Rep issue - please notice the following behavior:
The Monthly Cum measure returns a value of zero for Frank V. in Jan/Feb, then self-corrects in March. Same behavior for Harold G. in Jan only. Source data attached.
@kjssdca When you are checking for “not is 0”, Power BI is generating a query which filters out all of the Executives who have zeroes in grand total.
Interal query generated for your visual:
This is what Variable __ValueFilterDM1 returns, notice that Frank isn’t included.
Don’t add 0 manually and it will work as long as you are not modifying the Grand Total.
Zoho Sales v7_eDNA.pbix (1.4 MB)
3 Likes
Hi @kjssdca, did the response provided by @Greg and @AntrikshSharma help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
Thank you @AntrikshSharma
So I did that on my version of the file - making it match yours, refreshed everything and still the Cumulative Total is not correct. How do I get into the DAX editor for the query in your screenshot?
@kjssdca That’s the code that Power BI generates for populating the data points in any visual, you can get it by using the Performance Analyzer in the view tab.
Alternatively, you can use the file that I have attached earlier and proceed with it, also I turned off the automatic data time and it reduced the file size by 5-6 MBs.
Hi @kjssdca, did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
Hi @kjssdca, we’ve noticed that no response has been received from you since the 28th of February. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Thanks @AntrikshSharma - I can’t use the file that you sent back because the data is sanitized. Your solution absolutely makes sense - I just can’t understand why the change isn’t impacting my file in the same manner and I haven’t had the time to look into the Performance Analyzer and compare the code. I will try to get to that soon.
The post was helpful but has not solved the issue, so I have not yet marked it so.
Hi @kjssdca
Please try this, clear the filter for Monthly Cum and add the measure Closed Sales to the filter panel and set up to >0
1 Like
That worked! Thank you very much - such a simple solution! Much appreciated!
1 Like