Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
I need to create a new column by looking up distinct values from "Bid Number" and "Class" and sum up the "bid value" in the new column. Any help is greatly appreciated.
for example: I want to create "Bid value total" column based on unique values where "Bid Number" & "class" are same.
Bid Number Bid value class Bid value total
151 10 a
151 10 a 20
151 10 b
151 5 b 15
152 10 b
152 20 b 30
Solved! Go to Solution.
@n2p2gupta wrote:
I need to create a new column by looking up distinct values from "Bid Number" and "Class" and sum up the "bid value" in the new column. Any help is greatly appreciated.
for example: I want to create "Bid value total" column based on unique values where "Bid Number" & "class" are same.
Bid Number Bid value class Bid value total
151 10 a
151 10 a 20
151 10 b
151 5 b 15
152 10 b
152 20 b 30
You can either create a calculated column
bid value total colum = CALCULATE ( SUM ( yourTable[Bid value] ), FILTER ( yourTable, EARLIER ( yourTable[Bid Number] ) = yourTable[Bid Number] && EARLIER ( yourTable[class] ) = yourTable[class] ) )
Or create a measure and put data in a Table visual.
bid value total measure = CALCULATE ( SUM ( yourTable[Bid value] ), ALLEXCEPT ( yourTable, yourTable[Bid Number], yourTable[class] ) )
@n2p2gupta wrote:
I need to create a new column by looking up distinct values from "Bid Number" and "Class" and sum up the "bid value" in the new column. Any help is greatly appreciated.
for example: I want to create "Bid value total" column based on unique values where "Bid Number" & "class" are same.
Bid Number Bid value class Bid value total
151 10 a
151 10 a 20
151 10 b
151 5 b 15
152 10 b
152 20 b 30
You can either create a calculated column
bid value total colum = CALCULATE ( SUM ( yourTable[Bid value] ), FILTER ( yourTable, EARLIER ( yourTable[Bid Number] ) = yourTable[Bid Number] && EARLIER ( yourTable[class] ) = yourTable[class] ) )
Or create a measure and put data in a Table visual.
bid value total measure = CALCULATE ( SUM ( yourTable[Bid value] ), ALLEXCEPT ( yourTable, yourTable[Bid Number], yourTable[class] ) )
Thanks Eric!