Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Apply distinct rows in measures

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 numberNet Value
101000
101000
101000
101000

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).
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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!

     

6 Replies

  • dobregon's avatar
    dobregon
    Icon for Impactful Individual rankImpactful Individual
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        AllisonKennedy
        Icon for Community Champion rankCommunity Champion
        5 years ago

        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's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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. 

     

     

  • dobregon's avatar
    dobregon
    Icon for Impactful Individual rankImpactful Individual
    5 years ago

    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

    OrderNumberNetValue
    101000
    101000
    101000
    15500
    15500
    15500
    15500
    22000
    22000
    22000
    22000
    22000

     

    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.

    OrderNumberNetvalue
    101000
    15500
    22000


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






  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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!