Forum Discussion

sborowiec13's avatar
sborowiec13
Regular Visitor
8 years ago
Solved

Creating a new table with the first calculated value for each field name

I have a variety of different customer numbers in my data and using fixed calculations, I have been able to get the numbers I need on a category basis (had to do a couple of fixed calculations on age*amount and amount. However, at the very end of this process, I need to sum up those values on a customer basis, not record basis. 

 

Customer 1 50

Customer 1 50

Customer 1 50

Customer 3 30

Customer 3 30

 

Right now I'm getting a sum of 210, when I really need a sum of 80. I've tried EVERYTHING I can think of to get this and I just can't get to it. 

 

Hoping someone can help with a good solution here!

  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi sborowiec13,

     

    If the sample you posted is the data source, you can try out a measure like this.

    Measure =
SUMX (
    SUMMARIZE (
        'Table1',
        'Table1'[Customer],
        Table1[Value],
        "Unique", DISTINCT ( 'Table1'[Value] )
    ),
    [Value]
)

    Creating_a_new_table_with_the_first_calculated_value_for_each_field_name

     

    Best Regards,

    Dale

1 Reply

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi sborowiec13,

     

    If the sample you posted is the data source, you can try out a measure like this.

    Measure =
SUMX (
    SUMMARIZE (
        'Table1',
        'Table1'[Customer],
        Table1[Value],
        "Unique", DISTINCT ( 'Table1'[Value] )
    ),
    [Value]
)

    Creating_a_new_table_with_the_first_calculated_value_for_each_field_name

     

    Best Regards,

    Dale