Forum Discussion

WishAskedSooner's avatar
WishAskedSooner
Continued Contributor
2 years ago
Solved

Weighted Average on Narrow Table Using DAX Measure

My Fact Table is quite narrow with a few Dimension tables (think EAV model if you are familiar with dB architecture). Let's say I have the following 'Fact' table:   EntryID AttrID Value 1 ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi WishAskedSooner ,

    First of all, many thanks to lbendlin  for your very quick and effective replies.

    Based on my testing, please try the following methods:

    1.Create the simple table.

    2.Create the new measure to calculate sum across all entry id.

    Weighted Average = 
DIVIDE(
    SUMX(
        FILTER(
            'Fact Table',
            RELATED('Dim Table'[Category]) = "A"
        ),
        'Fact Table'[Value] * LOOKUPVALUE('Fact Table'[Value], 'Fact Table'[EntryID],'Fact Table'[EntryID], 'Dim Table'[Category], "B")
    ),
    CALCULATE(SUM('Fact Table'[Value]), ALL('Dim Table'), 'Dim Table'[Category] = "B")
)

    3.Select the measure and edit the number of shown for the value.

    4.Drag the measure into the card visual. The result is shown below.

     

    You can also view the following links to learn about DAX function.

    LOOKUPVALUE function (DAX) - DAX | Microsoft Learn

    SUMX function (DAX) - DAX | Microsoft Learn

     

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

WishAskedSooner's avatar
WishAskedSooner
Continued Contributor
2 years ago

Thank you for your reply. You seem to imply that I could use either SUMX or PRODUCTX to get to the correct value which is interesting.

 

However, I am very new to PowerBI and DAX, so if you could provide the syntax with how to use those functions with or without CALCULATE, that would be a huge help. As I mentioned above, I have researched this for at least four hours without success, and this includes experimenting with SUMX and PRODUCTX.

 

I am not sure what you mean by providing sample data. I can't upload my actual PBI model due to security reasons. I suppose I could create a PBI project with the data that I included above, but is that extra effort really necessary?

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    It's totally your choice.  I can only assist with meaningful sample data.