Avoid Summing Values with Duplicate Fields

[BHigh37]

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.

[v-eachen-msft · Accepted solution]

Hi @BHigh37 ,

You could use ISINSCOPE() function to set total values.

Here is my test file for your reference.

[BHigh37]

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.

[amitchandak]

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-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601