Forum Discussion

MA's avatar
MA
Advocate I
9 years ago
Solved

Weighted Avg Aggregation in DAX

Hi,    I have already looked around a bit, but didn't find any solution to my problem.  I have two columns in my table ("Apples Sold" and "Profit per Apple"). In addition I have numerous types o...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    MA,

    Create the following measures in your table and create a bar chart as shown in the following screenshot.

    Sum of Pieces Sold = CALCULATE(SUMX(Table2,Table2[Pieces Sold]*Table2[USD/lb]),FILTER(Table2,Table2[Type]="Red" ||Table2[Type]="Yellow"||Table2[Type]="Green"))

    Sum of USD/lb = CALCULATE(SUM(Table2[Pieces Sold]),FILTER(Table2,Table2[Type]="Red" ||Table2[Type]="Yellow"||Table2[Type]="Green"))

    Average = [Sum of Pieces Sold]/[Sum of USD/lb]

    Measure = IF(ISFILTERED(Table2[Type]),
       SUM(Table2[USD/lb]),
        [Average]
    )



    Regards,

Anonymous's avatar
Anonymous
Not applicable
9 years ago

MA,

Create the following measures in your table and create a bar chart as shown in the following screenshot.

Sum of Pieces Sold = CALCULATE(SUMX(Table2,Table2[Pieces Sold]*Table2[USD/lb]),FILTER(Table2,Table2[Type]="Red" ||Table2[Type]="Yellow"||Table2[Type]="Green"))

Sum of USD/lb = CALCULATE(SUM(Table2[Pieces Sold]),FILTER(Table2,Table2[Type]="Red" ||Table2[Type]="Yellow"||Table2[Type]="Green"))

Average = [Sum of Pieces Sold]/[Sum of USD/lb]

Measure = IF(ISFILTERED(Table2[Type]),
   SUM(Table2[USD/lb]),
    [Average]
)



Regards,

MA's avatar
MA
Advocate I
9 years ago

Thank you very much! This is exactly what I was looking for. However, I would call your first measure "Sum of USD/lb" and the second one "Sum of Pieces Sold". This makes more sense and is easier to understand for others who might stumble upon this thread.