Sum values group by a few categories with conditions

[RogerSteinberg]

Hi,

I have a table that looks like this: 

User ID	member	category A	category b	Amount	Transaction Date	Enter Date	Source	Order ID
123	abc	aa	bb	$       1.00	2/8/2021 0:00	2/8/2021 0:00	O	987
123	abc	aa	bb	$       1.00	2/8/2021 0:00	2/9/2021 0:00	O	987
999	zzz	aa	bb	$       1.00	2/8/2021 0:00	2/8/2021 0:00	Z	321
999	zzz	aa	bb	$       1.00	2/8/2021 0:00	2/8/2021 0:00	Z	321

I actually have way more columns than this but all i need is to create a measure  that calculates the total amount generated each day without duplicating the values with the following two conditions:

1. category A is AA and

2. category B is BB

My total amount should be 2$.

So essentially in SQL it should be 

select transaction_date,sum(amount) as total

from (

select transaction_date,order_id, amount

from table

where categoryA = 'aa' and categoryB = 'bb'

group by transaction_date, order_id,amount ) as base

group by transaction_date

[v-robertq-msft · Accepted solution]

Hi, @RogerSteinberg 

According to your SQL query and sample data, I can understand the output you want to get, we usually use “Calculated table” to achieve the “Select” function in SQL, you can create a Calculated table like this:

This is my test data based on your sample data:

Table 2 =

SUMMARIZE(

    FILTER('Table',[category A]="aa"&&[category b]="bb"),

    'Table'[Transaction Date],

    'Table'[Order ID],

    "Total",SUM('Table'[Amount]))

And you can get what you want, like this:

You can download my test pbix file here

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vanessafvg]

well this calculation will give you that answer but it just depends on how you want to use it, in an aggregated visual, or in a row context?

total amount = CALCULATE(sum(data[Amount]), FILTER( data, data[category A] = "aa" && data[category b] ="bb"))

[RogerSteinberg]

that solution is not grouping by the dimensions. it will add duplicate values

[vanessafvg]

Hi yes Roger that is why I asked how do you want to use this measure, that will define the solution, please give an example of what kind of visual you want to use it in, or how you plant to use this information.   Creating this measure as a calculated column will always give you total of 2 in any context.