cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gelain
Frequent Visitor

Add maximum values ​​per column

Hi,

 

In the case of a sale in the amount of U $ 1000.00, it was received in two times of U $ 500.00. The information is generated in the table of the SQL database, as shown:

 

IDSale ValueReceived
1                      1.000,00                     500,00
1                      1.000,00                     500,00
2                         450,00                     225,00
2                         450,00                     225,00
   
Total                      1.450,00                 1.450,00

 

I need the sum to be performed by a single ID as to the value of the sale and total as to the receipts. I made a few attempts with MAX, but there are problems in the graphics and sums, because it only generates the maximum value.

 

I need a lot of help.

 

Thank you.

1 ACCEPTED SOLUTION
pawel1
Continued Contributor
Continued Contributor

Hi Gelain,

have you tried with the FIRSTNONBLANK function:

Sales Value correct sum = SUMX(DISTINCT(Data[ID]), FIRSTNONBLANK(Data[Sales Value], 0))

I got the correct total:

 

firstnonblank.JPG

more details:

https://stackoverflow.com/questions/43547620/sum-distinct-values-for-first-occurance-in-power-bi

View solution in original post

3 REPLIES 3
v-chuncz-msft
Community Support
Community Support

@Gelain,

 

You could use the following measure as well.

Measure =
SUMX ( VALUES ( Table1[ID] ), CALCULATE ( MAX ( Table1[Sale Value] ) ) )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
pawel1
Continued Contributor
Continued Contributor

Hi Gelain,

have you tried with the FIRSTNONBLANK function:

Sales Value correct sum = SUMX(DISTINCT(Data[ID]), FIRSTNONBLANK(Data[Sales Value], 0))

I got the correct total:

 

firstnonblank.JPG

more details:

https://stackoverflow.com/questions/43547620/sum-distinct-values-for-first-occurance-in-power-bi

View solution in original post

Gelain
Frequent Visitor

Thank you. Helped me a lot.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors