Forum Discussion

CM_Mills's avatar
CM_Mills
Helper II
3 years ago
Solved

Calculation Error

Hi,

I have a calculation that isn't working as expected when using two different data points.

 

Looking at the example below i want to be able to if i filter on just 'data 1' get the result 5. 

Then if i filter on 'data 1' and 'data 2' together it should result in 15,046. Where the calculation is done one at a time then added together.

 

However I’m currently getting the result of the 'Number' added together and the 'multiplier' added together and then TIMES each other resulting in 16,573. 

 

My query is:

Revenue - Total Including Predicted (USD) = (SUMX('Revenues','GST Revenues'[Predicted Revenue Calculator (USD)]*SUMX(Data,Data[Volume - Lots])))+SUMX('GST Revenues','GST Revenues'[Revenue - Total (USD)])

 

I also have a unique identifier for each data point if required: ‘Unique Identifier (Inc. Year & Month)

 

 

Result Wanted

   
    
 NumberMutiplierResult
Data 1            42           0.13             5
Data 2   11,339           1.33   15,041
      15,046
    
    
Currently Happening   
    
 NumberMutiplierResult
Data 1            42           0.13 
Data 2   11,339           1.33 
    11,381           1.46   16,573
  • PabloDeheza's avatar
    PabloDeheza
    3 years ago

    Not sure I follow correctly how the model is built, but for the tables is see in the images, this should work.

    SUMX(
    SUMMARIZE(
        'YourTable',
        'YourTable'[Identifier],
        'YourTable'[Multiplier],
        'YourTable'[Number]
    ),
    'YourTable'[Number] * 'YourTable'[Multiplier]
)

     

5 Replies

  • PabloDeheza's avatar
    PabloDeheza
    Solution Sage
    3 years ago

    Hi there!

    Have you tried only this:

    Revenue - Total Including Predicted (USD) =
SUMX(
    'Revenues',
    'GST Revenues'[Predicted Revenue Calculator (USD)] * Data[Volume - Lots]
)
    • CM_Mills's avatar
      CM_Mills
      Helper II
      3 years ago

      Hi, Thanks for reviewing. Without the sumx before the volume i get the following:

       

      A single value for column 'volume - lots' in table 'data' cannot be determined. This canhappen when a measure refers to a column that contains many values without specifying an aggregation such as min,m ax,count or sum to get a single result.

      • PabloDeheza's avatar
        PabloDeheza
        Solution Sage
        3 years ago

        Not sure I follow correctly how the model is built, but for the tables is see in the images, this should work.

        SUMX(
    SUMMARIZE(
        'YourTable',
        'YourTable'[Identifier],
        'YourTable'[Multiplier],
        'YourTable'[Number]
    ),
    'YourTable'[Number] * 'YourTable'[Multiplier]
)