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 andZoho Sales v7_eDNA.zip (5.6 MB)
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.
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)
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?