Forum Discussion
Avoid Summing Values with Duplicate Fields
I've been reading about duplicate values but cannot find what I'm looking for. I'm tracking stats of sales data in restaurants. We have various account types that can pay for checks. I need to track the accounts without doubling up on the sales data. Consider the following data. Two people ate in the restaurant and split the bill.
| Check | Account | Covers | Sales |
| 12345 | A2002 | 2 | $100 |
| 12345 | A2003 | 2 | $100 |
Results that need to be returned:
1 distinct check
2 distinct accounts
2 covers
$100 in sales
I have a report setup already, only problem is I'm double reporting some covers and sales numbers. I have a matrix table and various cards (single and multi-row). This is a simplified example, but the point gets accross. I want to report totals at the bottom of the covers and sales columns to be 2 and $100, respectively, while still allowing me to report the distinct number of accounts that were used.
I'm sure there is a DAX way to do this in modeling, but I cannot wrap my head around it.
Hi BHigh37 ,
You could use ISINSCOPE() function to set total values.
Here is my test file for your reference.
3 Replies
If you want to show data check level or above
one of the following should work
Sales1 = Max(sales) New Sales = sumx(values[sales[checks]),Sales1 ) Or CALCULATE(sumx((SUMMARIZE(Sales,sales[checks],"s1",SUM(Sales[Sales]))),[s1])Another option is using all except create count rows at check level and divide it
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners/ba-p/890814
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601Hi BHigh37 ,
You could use ISINSCOPE() function to set total values.
Here is my test file for your reference.
Thank you v-eachen-msft , I think this will do it. The test file is a tremendous help. I need to further my understanding of the syntax, but my initial test with my live data is starting to show the results I need.
