Forum Discussion

AMJG851126's avatar
AMJG851126
New Member
7 years ago
Solved

Multiplying rows across tables as a measure

Hi there

 

I am new to Power BI and am struggling to create an excel sumproduct powerbi equivalent.

 

I have two tables, one named Dates and the other Financials. A simplified version of the tables is below

 

Dates

YearMonth           YTD%

201801                 53%

201802                 47%

 

Financials

YearMonth           BalanceType          Value

201801                 Credit Balance        100

201801                 Debit Balance          50

201802                 Credit Balance         150

201802                 Debit Balance          40

I have created a relationship based on YearMonth.

 

I am struggling to create a measure that will multiply the value with the YTD% and then return the sum based on BalanceType. It seems to first add all Valu and then multiply with the sum of the YTD%. Any ideas how I could achieve this?

Thanks

Need Help
  • TomMartens's avatar
    TomMartens
    7 years ago

    Hey,

     

    I guess this will work, make sure you use the YearMonth column from the Dates table in all of your visuals:

    Measure = 
SUMX(
    'Financials'
    , [Value] * RELATED(Dates[YTD%])
)

    Using this measure and your sample data I can create this table:

    Hopefully this is what you are looking for.

     

    Regards,
    Tom

     

4 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    7 years ago

    Hey,

     

    I guess this will work, make sure you use the YearMonth column from the Dates table in all of your visuals:

    Measure = 
SUMX(
    'Financials'
    , [Value] * RELATED(Dates[YTD%])
)

    Using this measure and your sample data I can create this table:

    Hopefully this is what you are looking for.

     

    Regards,
    Tom

     

    • AMJG851126's avatar
      AMJG851126
      New Member
      7 years ago

      Hi Tom

       

      Thank you for your solution, it has really unlocked a lot for me. 

       

      The idea was that i could not have the YearMonth in all my visuals because I mostly report yearly figures. It does however seem to work without it. Please let me know if there is something i should look out for.

       

      Thank you!

      Antoine 

      • TomMartens's avatar
        TomMartens
        Icon for Super User rankSuper User
        7 years ago

        Hey Antoine,

         

        as long as the year column is from the same table as the yearmonth, it will work flawless.

         

        Regards,

        Tom