Sum of distinct values

[sam466113]

Hi all,

I am new PowerBi and exploring various options.

Please find below the data table. Table name is "Table1"

Name	Order Number	Sub Order	Order Amount	Receipt Number	Sub Receipt Number	Amount
A	123	1	10	456	1	32
A	123	1	10	789	1	32
A	123	2	20	456	2	32
A	123	2	20	789	2	32
B	852	1	25	753	1	60
B	852	2	30	753	2	60
C	852	1	15	894	1	17

I am tryting to create a calculation that will give below given values:

- If I select Name =A in filter or Order Number = 123 in filter then it should give total as 30 (based on column Order Amount)

 and other calulation that will give Sum of Amount as 32 (based on column Amount)

- If I select Name = B or Order Number = 852 on the filter then it should give total as 55 (Based on Column Order Amount)

and  other calulation that will give Sum of Amount as 60 (based on column Amount)

If i remove all filter then Sum of Order Number should be 100 

and sum of Amount should be 109.

Can someone please help me here with these 2 calculations.

Thanks in advance.

Sam

[lbendlin]

Learn about the usage of SUMMARIZE or SUMMARIZECOLUMNS.  In your case the grouping must include the Sub Order column and use MAX aggregation for the Order Amount.  

It is also not recommended to mix orders and receipts this way - these are independent facts.