Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
Kudo Kingpin
Kudo Kingpin

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
Kudo Kingpin
Kudo Kingpin

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

Gelain
Frequent Visitor

Thank you. Helped me a lot.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.