Summarise Non Unique Values

[grega]

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.

[FreemanZ]

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])
)

[grega]

Hi

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   

[FreemanZ]

hi @grega 

still quite unclear. could you post your expected result?

[grega]

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. 

[grega]

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.

[FreemanZ]

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

[grega]

Hi,

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

Thanks,

Greg.

[FreemanZ]

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]))
)

[lbendlin]

use DISTINCT()

[grega]

Thanks.