Forum Discussion

zeckert's avatar
zeckert
Helper I
9 years ago
Solved

Need to sum based on unique values

I have a Purchase order report that i need to be able to group by vendor but there is multiple rows for one PO due to line items.  There is a column that has the final amount but I keep getting it co...
Need Help
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    zeckert

     

    Hi,

     

    Try with this Measure:

     

    TotalOrderAmount = SUMX(DISTINCT(Table1[PO#]),CALCULATE(AVERAGE(Table1[Order Amount])))

     

    Regars

     

    Victor

    Lima - Peru

zeckert's avatar
zeckert
Helper I
9 years ago
So the desired look i would want is listed below Vendor1 627.91 Vendor2 150.39 The order amount will always be the same because that is the final amount of the purchase order however there is multiple rows to account for each item in order. So it will read 583 at least once for the main line but read at least one other line for the item in the order. I need to be able to take the amount one time based on the PO # and sum all orders under vendor1 then same for vendor2 and so on
Vvelarde's avatar
Vvelarde
Community Champion
9 years ago

zeckert

 

Hi,

 

Try with this Measure:

 

TotalOrderAmount = SUMX(DISTINCT(Table1[PO#]),CALCULATE(AVERAGE(Table1[Order Amount])))

 

Regars

 

Victor

Lima - Peru

  • Mosa's avatar
    Mosa
    New Member
    7 years ago

    Hi.

     

    Thanks for your help. I have a question with the same context. Now, when I unpivot comlumns, data is repeated. So, I want to sum the number of values in one column, but it appreas as doubled

  • zeckert's avatar
    zeckert
    Helper I
    8 years ago

    Thank you so much.  It worked perfect.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Vvelarde, 
    The Measure "TotalOrderAmount = SUMX(DISTINCT(Table1[PO#]),CALCULATE(AVERAGE(Table1[Order Amount])))" works perfectly but I can not seem to wrap my head around how it works. Could you provide an explaintation? 
    Thanks

  • NaveenVerma's avatar
    NaveenVerma
    Regular Visitor
    7 years ago

    Hi

     

    Can we also use filter in the below formula?

     

    TotalOrderAmount = SUMX(DISTINCT(Table1[PO#]),CALCULATE(AVERAGE(Table1[Order Amount])))

     

    Regards,

    Naveen Verma

  • axelBose's avatar
    axelBose
    Frequent Visitor
    6 years ago

    Thank you, Victor Vvelarde Need to sum based on unique values .

     

    I have uesd this same measure in one of my reports. However, when I reference it in the following, I do not get the correct output.

     

    Upper bound =
    VAR __AVG = sumX(distinct('Order History'[MaterialPlantKey]),calculate(average('Order History'[AvgNo0_Corrected Orders])))
    VAR __STDEV = sumX(distinct('Order History'[MaterialPlantKey]),calculate(average('Order History'[StdDevNo0_Corrected Orders])))
    VAR __UPPER_BOUND = __AVG + __STDEV *[Sigma level Value]
    return ( __UPPER_BOUND)
     
    In the above measure I am taking the distinct values by materialplant key for the average and standard deviation of orders in order to calculate the upper bound. The sigma level is simply the number of standard deviations that the user selects in the report with a slicer.

    Thank you,
    Axel
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    This worked but still dont understand why we need to use AVERAGE instead of SUM.