Apply distinct rows in measures

[Anonymous]

Hi all,

I stumbled upon a new problem:

Like mentioned in an earlier post I got a table with duplicated (multiplicated, up to over a dozen times) values.

Means: Order number 10 contains 4 items and has a cumulated value of 1000$. Then it displays:

Order number	Net Value
10	1000
10	1000
10	1000
10	1000

Thats a problem because when I want to calculate the average order value, these values will falsificate the outcomes (4000$ instead of 1000$). At the other hand I can't just simply eliminate the multiplicated rows in PowerQuery as I'll need them for other measurements later.

So I created a table with distinct values, using

Distincttable = DISTINCT(SELECTCOLUMNS( 'Table1', "Order number",'Table1'[Order number]))

which show every order number only once and established a single-direction relationship with the original table (Table1).

However, if I want to create measurements (by example 

Net Value divided by Count of Sales Doc. =

DIVIDE(SUM('Table1'[Net Value]), COUNTA('Distinct'[Order number]))

it divided the cumulated net value of the table with the multiplicated values through the count of the table with the distinct order numbers- obviously also the wrong outcome.

Therefore I need to know if there is any measure where I can apply the filter of the distinct table to the Net Value (or other values, later on) - so that PowerBI doesn't aggregate all Net Values but only once per Order Number.

Alternatively, I'd need a formula probably which allows me to transfer the respective values (in this case the Net Value) to the distinct table. This would have the same outcome as I could just use the Net Value from the distinct table for my calculations then (as it would be only transfered one time, as this table contains the order number only once).

[v-kelly-msft · Accepted solution]

Hi @Anonymous ，

I created a sample table as below:

First go to query editor,add an index column;

Then you need to create a measure as below:

_average = 
var minindex=CALCULATE(MIN('Table'[Index]),FILTER(ALL('Table'),'Table'[NetValue]=MAX('Table'[NetValue])&&'Table'[OrderNumber]=MAX('Table'[OrderNumber])))
var _value=IF(MAX('Table'[Index])=minindex,MAX('Table'[NetValue]),BLANK())
var _rows=CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[NetValue]=MAX('Table'[NetValue])&&'Table'[OrderNumber]=MAX('Table'[OrderNumber])))
Return
DIVIDE(_value,_rows)

Or you could create below calculated columns instead to get the same result:

_net value = 
var index=CALCULATE(MIN('Table'[Index]),FILTER('Table','Table'[NetValue]=EARLIER('Table'[NetValue])&&'Table'[OrderNumber]=EARLIER('Table'[OrderNumber])))
Return
IF('Table'[Index]=index,'Table'[NetValue],BLANK())

_col average = 
var _rows=CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[NetValue]=EARLIER('Table'[NetValue])&&'Table'[OrderNumber]=EARLIER('Table'[OrderNumber])))
Return
IF(ISBLANK('Table'[_net value]),BLANK(),DIVIDE('Table'[_net value],_rows))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[dobregon]

Hi @Anonymous 

1. Those are both measures arent they? Could I also use them to create said second column in the distinct table?
There are measures, you can put in whatever visual what you want

2. I don't really get how those functions work together... Like, is it two methods and I can choose either one or do I have to use both together?

There are 2 examples of measures one doing a max and the other doing the average

SumX = 
Var A = max(Netvalue)
Var B = SUMX(distinct(order number), A)
RETURN 
B


AverageX= 
Var A = max(Netvalue)
Var B = AVERAGEX(distinct(order number), A)
RETURN 
B

3. I also don't really understand how they work (but I have to to ensure it does what we need it to do) :

Myabe i didnt understand your example, but as i understood before you have something like this table

OrderNumber	NetValue
10	1000
10	1000
10	1000
15	500
15	500
15	500
15	500
2	2000
2	2000
2	2000
2	2000
2	2000

So, you have many netvalues repetead by ordernumber and you want to sum the values by distinct order number.
The sumx or averagex function will "create" a virtual table with the unique numbers of ordernumber and to create the function you selected the "max" that when the system create the virtual table when it needs to agregate the ordernumber the max is the aggregation function, but if its the same value (that should be in the example) it is the same to use for aggreations the max, min, average.

OrderNumber	Netvalue
10	1000
15	500
2	2000

and the sumx will give to you a 3500 and the averagex will show you something like 1166.66667 

[dobregon]

Hi @Anonymous 

Maybe you can use the functions AverageX and SumX, for example:

SumX = 
Var A = max(Netvalue)
Var B = SUMX(distinct(order number), A)
RETURN 
B


SumX = 
Var A = max(Netvalue)
Var B = AVERAGEX(distinct(order number), A)
RETURN 
B

these functions will give to you the sum or de average for one unique order number (and if you have the same order number repetead with different net value, they will take the max value).

In reality the sumx, averagex functions will create a "virtual table" with unique order numbers agregatted by max net value.

hope this helps

[Anonymous]

Hi @dobregon just for me to understand (I have to cause if I make mistakes with the data Im done):

1. Those are both measures arent they? Could I also use them to create said second column in the distinct table?

2. I don't really get how those functions work together... Like, is it two methods and I can choose either one or do I have to use both together?

3. I also don't really understand how they work (but I have to to ensure it does what we need it to do) :

Var A then grabs the Net Value for a number given by Var B which sums/ averages all values grabbed by Var A and RETURN B gives the resulting number?

Is that hot it works?

EDIT:

I tried doing it with the LOOKUPVALUE-Function with the following expression:

Net Value = LOOKUPVALUE('UNION'[Net value], 'UNION'[Sales Doc.],'Distincttable'[Sales Doc.],0)

However, while its working for some values, half of the values are 0 (meaning: The Sales Doc. number looked up could not be found in the raw table) and I'm getting increasingly frustrated over that **bleep** program as it must be in the raw table cause that is where the Sales Doc. number came from in the first place!

[AllisonKennedy]

@Anonymous  a zero value could also mean there is more than one value for Union[NetValue] for that Sales Doc number. Which is why I chose to use MAX in my function I gave you - it will always provide an answer if the Sales Doc is in the table. Seems like there may be some data quality issues if you expect the net value to be always the same.

[AllisonKennedy]

Pulling the Net Value from fact table into Distinct table is tricky, but you might be able to achieve what you want using MAX or AVERAGE. 

Net Value divided by Count of Sales Doc. =

DIVIDE(MAX('Table1'[Net Value]), COUNTA('Distinct'[Order number]))

You can also try using SUMX in your measure to provide the row context for some aggregations if needed.