Forum Discussion
Table filtering and not summing correctly
Am tyring to understand what i am doing wrong to make these numbers not match up. I currently have 2 tables;
1. Looks at date, location and number sales reps worked that day
2. looks at sales that have been done by store name, date, location, sales and Sales Rep
What i am trying to do is based on the above tables is to create a table that looks at Store name and total sales/number of reps worked that day. The issue i have having is the numbers just dont match up example below;
as no date is being selected all the numbers under measure should be 1386 for each store as number of store reps should be the same per store. But for some reason it is changing the number based on store.
The measure is just a sum sales reps worked that day. For the count divide by days worked this is not summing currently either as is summing to a diff number to 4.03. Any help would be great
HI Anonymous
This looks like a measure totals problem. Very common. See this post about it
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907If not your case, please share your sample pbix file and your expected output.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490Regards,
lin
3 Replies
- amitchandak
Super User
Anonymous , not very clear.
It should be like
averageX(summarize(Table, Table[Date], "_1", sum(Table[Sales]), "_2",distinctcount(Table[Reps])),divide([_1],[_2]))
Sorry that doesnt work it gives me the following;
they dont sum up correctly and the number of sales reps working is changing per store it should be the same number for them all so the 3.92 should be split out by store
v-lili6-msftCommunity Support
HI Anonymous
This looks like a measure totals problem. Very common. See this post about it
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907If not your case, please share your sample pbix file and your expected output.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490Regards,
lin
