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

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

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