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,

cs_skit's avatar
cs_skit
Resolver IV
9 years ago

maybe you give some more visual details what you want

 

 

weighted average sounds like SUMX calc but I am unsure in which form exactly you need the result

MA's avatar
MA
Advocate I
9 years ago

Thanks for the quick reply, here are some visuals:

Let's assume this data:

the report looks similar to this:

The graphs work fine when a type is selected, but there is no appropriate way to show the total USD/bbl value when no type is selected. Furthermore, I have subgroups to types (red juicy, red sweet to red), but I already accepted that I probably have to work without them, as they would somehow need to be excluded in a weighted average calculation. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    MA,

    What value do you want to display for USD/lb when no type is selected in slicer? When you choose "Average" for USD/lb in the chart, does it return your expected result?

    Regards,

    • MA's avatar
      MA
      Advocate I
      9 years ago

      Ignoring the subtypes, the solution would calculate as follows:

      Value = (50*4.80 + 20*5.00 + 40*3.00)/(50 + 20 + 40) = ~4,18

      • 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,