Forum Discussion

DLamarche's avatar
DLamarche
Icon for Helper I rankHelper I
6 years ago
Solved

Need help with a simple formula / measure

Hello all,

I am new to BI but I'm working hard to learn with my own data. I've been trying something that I thought would take me 30 mins and 2.5 hours later I don't understand why I dont't get it right.

I'm using a single table in my Access database that I use in an Access Intro course.

My Calls table

This very simple table contains a field CallLength (in minutes) and CallRateApplied (in minute). Multiplying the two fields will return the Total price for the call. (ignore the apparent lack of normalisation, it's all good!)

I made a query summarising the Total amounts per month. I did a Pivot Table with the exact same data and I'm happy with the results.

 

Data massaged in Excel

Below in the data copied in Excel to double check the results in the query & Excel. The sum of the Total columns match the query in Access.

I made a Pivot Table with the data across and it returns the correct total for January for $3,032. (shown below)

Pivot table filtering for 2016

 

 

 

 

 

 

 

 

When I do the report using the same data PowerBI returns a completely different answer. See here the answer is 180,488.2.

In Power BI

I am not able reproduce the data that would yield that answer. Now I know that some of you will ask me to show the Measure I used for the calculation. There it is: Sales Amount = SUM(tblCalls[CallLength]) * SUM(tblCalls[CallRateApplied]).

I suppose that I could have created a calculated column in the Query Editor but I want to learn how to design measures on my own ... one day!

Anyone interested in my challenge??

Thanks for your time friends.

Daniel

  • DLamarche 

     

    pity the result from PBI isn’t correct: you would be very rich!

    try this:

    Sales Amount = SUMX(tblCalls,  SUM(tblCalls[CallLength]) * SUM(tblCalls[CallRateApplied])).

     

  • Hi Daniel,

    use the following measure:

     

    =SUMX(tblCalls, tblCalls[CallLength] * tblCalls[CallRateApplied])

     

    The function SUMX() iterates through the table tblCalls and row by row it multiplies CallLength by CallRateApplied. At the end it sums up all computed multiplications.

     

    FrankAT

  • DLamarche's avatar
    DLamarche
    6 years ago

    Thanks to both of you guys. One day I promise that when I'll be better at that stuff I will return the favour (Aussie spelling).

    I have to look more in depth ... again ... about the SUMX() function.

    Thanks a bunch again.   8-)

3 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion

    DLamarche 

     

    pity the result from PBI isn’t correct: you would be very rich!

    try this:

    Sales Amount = SUMX(tblCalls,  SUM(tblCalls[CallLength]) * SUM(tblCalls[CallRateApplied])).

     

    • DLamarche's avatar
      DLamarche
      Icon for Helper I rankHelper I

      Thanks to both of you guys. One day I promise that when I'll be better at that stuff I will return the favour (Aussie spelling).

      I have to look more in depth ... again ... about the SUMX() function.

      Thanks a bunch again.   8-)

  • FrankAT's avatar
    FrankAT
    Icon for Community Champion rankCommunity Champion

    Hi Daniel,

    use the following measure:

     

    =SUMX(tblCalls, tblCalls[CallLength] * tblCalls[CallRateApplied])

     

    The function SUMX() iterates through the table tblCalls and row by row it multiplies CallLength by CallRateApplied. At the end it sums up all computed multiplications.

     

    FrankAT