topic Re: Summarise Non Unique Values in DAX Commands and Tips

Summarise Non Unique Values

grega — Thu, 02 Mar 2023 11:34:13 GMT

Hi,

I have a problem that I hope someone will be able to help me with.

I need to summarise a table containing the Customer Name, Order Number, Stock Reference, Cost Price, Sale Price, and Margin Percentage.

If the Cost Price and the Sale Price are identical and there are multiple rows for the order I want to summarise it into one row by Order number. Here is a data example:

Customer Name	Order Number	Stock Reference	Cost Price	Sale Price	Margin %
AB Smith	2354	KPCB_14768	15	36.37	58.76
AB Smith	2354	KPCB_14768	15	36.37	58.76

However if there is an order that has multiple rows but different Cost/Sale Prices I don't want to summarise that data. Here is an example:

Customer Name	Order Number	Stock Reference	Cost Price	Sale Price	Margin %
T K Anderson	2355	BS10_54674	17.5	32.96	54.49
TK Anderson	2355	BS09_34889	19	40	52.5

I hope this makes sense.

Thanks for your help,

Greg.

Re: Summarise Non Unique Values

lbendlin — Sat, 04 Mar 2023 01:33:16 GMT

use DISTINCT()

Re: Summarise Non Unique Values

FreemanZ — Sat, 04 Mar 2023 04:53:06 GMT

hi grega

not sure what column do you want to summarize. Let me suppose you want the general Margin%, then try like:

Margin% = VAR _table = SUMMARIZE( TableName, TableName[Customer Name], TableName[Order Number], TableName[Stock Reference], TableName[Cost Price], TableName[Sale Price] ) RETURN 1 - DIVIDE( SUMX(_table, TableName[Cost Price]), SUMX(_table, TableName[Sale Price]) )

Re: Summarise Non Unique Values

grega — Mon, 06 Mar 2023 08:23:12 GMT

Thanks for the reply.

I'm trying to summarise all the columns if multiple rows are itentical for an order. So if you have multiple rows where the Cost Price, Sale Price and Margin % are the same, sumarise. If there are orders where the Cost Price, Sale Price and Margin % on an order are different I don't want to summarise. I hope that is clear.

Thanks,

Greg.

If there are

Re: Summarise Non Unique Values

grega — Mon, 06 Mar 2023 08:24:32 GMT

Thanks.

Re: Summarise Non Unique Values

FreemanZ — Mon, 06 Mar 2023 08:34:44 GMT

hi grega

still quite unclear. could you post your expected result?

Re: Summarise Non Unique Values

grega — Mon, 06 Mar 2023 09:02:19 GMT

Hi,

Sure, I'm probably not explaining this very well. With regard to the identical rows. I don't actually want to sum the values of the identical rows. I only want one row from multiple rows. For example, If I originally have this:

Id Cust Name Order No Stock Reference Cost Price Sales Price Margin %

1	AB Smith	2354	KPCB_14768	15	36.37	58.76
2	AB Smith	2354	KPCB_14768	15	36.37	58.76
3	AB Smith	2354	KPCB_14768	15	36.37	58.76

I need this:

Id	Cust Name	Order No	Stock Reference	Cost Price	Sale Price	Margin %
1	AB Smith	2354	KPCB_14768	15	36.37	58.76

Also, anything that is not a duplicate needs to display all the rows for the order, etc.

I hope this clears things up.

Thanks,

Greg.

Re: Summarise Non Unique Values

grega — Mon, 06 Mar 2023 09:09:19 GMT

Sorry, the table did not come out very well. Try these

Before:

Id	Cust No	Order No	Stock Ref	Cost Price	Sales Price	Margin %
1	AB Smith	2354	KPCD_14768	15	36.37	58.76
2	AB Smith	2354	KPCD_14768	15	36.37	58.76
3	AB Smith	2354	KPCD_14768	15	36.37	58.76

After:

Id	Cust No	Order No	Stock Ref	Cost Price	Sale Price	Margin %
1	AB Smith	2354	KPCD_14768	15	36.37	58.76

Thanks,

Greg.

Re: Summarise Non Unique Values

FreemanZ — Mon, 06 Mar 2023 09:20:03 GMT

hi grega

try to create a calculated table with:

Table = ADDCOLUMNS( SUMMARIZE( TableName, TableName[Cust No], TableName[Order No], TableName[Stock Ref], TableName[Cost Price], TableName[Sales Price], TableName[Margin %] ), "ID", CALCULATE(MIN(TableName[Id])) )

Re: Summarise Non Unique Values

FreemanZ — Mon, 06 Mar 2023 09:25:41 GMT

or you plot a table visual with all the needed column and choose "min" for the id colum and "don't summarize" for all other columns

Re: Summarise Non Unique Values

grega — Mon, 06 Mar 2023 10:32:44 GMT

Hi,

Ok, will have a look, I think think this will work.

Thanks,

Greg.