cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zeckert
Helper I
Helper I

Need to sum based on unique values

I have a Purchase order report that i need to be able to group by vendor but there is multiple rows for one PO due to line items.  There is a column that has the final amount but I keep getting it counted multiple times based on how many lines for that po there is.  I don't want to have to remove rows if i don't have to.  Sample Data listed below

 

I need vendor grouping with each PO# being counted once and add a total for each vendor.

 

VendorPO#Order AmountInventory #Invoice DateInventory Amt
Vendor1    284304583.37   
Vendor1    284304583.37S4228572.18/8/2017338.44
Vendor1    284304583.37S4228572.28/8/2017154.98
Vendor1    284304583.37S4228572.38/8/201790.36
Vendor2     284308140.7   
Vendor2     284308140.7S4228579.18/8/2017140.7
Vendor1    28430944.54   
Vendor1    28430944.54S4228592.18/7/201744.54
Vendor2     2843109.69   
Vendor2     2843109.69S4228610.18/7/20179.9
1 ACCEPTED SOLUTION
Vvelarde
Community Champion
Community Champion

@zeckert

 

Hi,

 

Try with this Measure:

 

TotalOrderAmount = SUMX(DISTINCT(Table1[PO#]),CALCULATE(AVERAGE(Table1[Order Amount])))

 

Regars

 

Victor

Lima - Peru




Lima - Peru

View solution in original post

11 REPLIES 11
deepvibha
Advocate II
Advocate II

Hi,
It is 'generally' recommended to 'unpivot' the Excel data while importing in Power BI. I have an Excel "Accounts Receivable" data maintained in the following "Outstandings" table:
Table "Outstandings"

MonthEntityDebtor GroupDebtor AgencyA. 30 days & lessB. 30 to 60 daysC. 60 to 90 daysD. 90 to 180 daysE. 180 to 240 daysF. 240 to 365 daysG. 365 to 730 daysH. 730 to 1095 daysI. 1095 days & Greater
Apr-18AAABBBCCC100020050025510002000600480550


After unpivoting and importing, it is like:

MonthEntityDebtor GroupDebtor AgencyAgeAmount
Apr-18AAABBBCCCA. 30 days & less1000
Apr-18AAABBBCCCB. 30 to 60 days200
Apr-18AAABBBCCCC. 60 to 90 days500
Apr-18AAABBBCCCD. 90 to 180 days255
Apr-18AAABBBCCCE. 180 to 240 days1000
Apr-18AAABBBCCCF. 240 to 365 days2000
Apr-18AAABBBCCCG. 365 to 730 days600
Apr-18AAABBBCCCH. 730 to 1095 days480
Apr-18AAABBBCCCI. 1095 days & Greater

550

 

The ER is 
Entity Table - 1 to many - Debtor Group Table
Debtor Group Table - 1 to many - Debtor Agency Table
Age  Table - 1 to many - Outstandings Table


There are 11 Entities, 12 Debtors and at least 18 Debtor Agencies in the dataset. Above is an example of One Month, for a single entity, debtor and debtor agency.
My query is how do I sum the amount so that I am able to slice it either/and by Entity, Debtor Group, Age?
Also what slicers should I have in my visuals?

Thanks
Deepak

zeckert
Helper I
Helper I

So the desired look i would want is listed below Vendor1 627.91 Vendor2 150.39 The order amount will always be the same because that is the final amount of the purchase order however there is multiple rows to account for each item in order. So it will read 583 at least once for the main line but read at least one other line for the item in the order. I need to be able to take the amount one time based on the PO # and sum all orders under vendor1 then same for vendor2 and so on
Vvelarde
Community Champion
Community Champion

@zeckert

 

Hi,

 

Try with this Measure:

 

TotalOrderAmount = SUMX(DISTINCT(Table1[PO#]),CALCULATE(AVERAGE(Table1[Order Amount])))

 

Regars

 

Victor

Lima - Peru




Lima - Peru

This worked but still dont understand why we need to use AVERAGE instead of SUM.

Thank you, Victor @Vvelarde Need to sum based on unique values .

 

I have uesd this same measure in one of my reports. However, when I reference it in the following, I do not get the correct output.

 

Upper bound =
VAR __AVG = sumX(distinct('Order History'[MaterialPlantKey]),calculate(average('Order History'[AvgNo0_Corrected Orders])))
VAR __STDEV = sumX(distinct('Order History'[MaterialPlantKey]),calculate(average('Order History'[StdDevNo0_Corrected Orders])))
VAR __UPPER_BOUND = __AVG + __STDEV *[Sigma level Value]
return ( __UPPER_BOUND)
 
In the above measure I am taking the distinct values by materialplant key for the average and standard deviation of orders in order to calculate the upper bound. The sigma level is simply the number of standard deviations that the user selects in the report with a slicer.

Thank you,
Axel

Hi

 

Can we also use filter in the below formula?

 

TotalOrderAmount = SUMX(DISTINCT(Table1[PO#]),CALCULATE(AVERAGE(Table1[Order Amount])))

 

Regards,

Naveen Verma

Anonymous
Not applicable

Hi @Vvelarde
The Measure "TotalOrderAmount = SUMX(DISTINCT(Table1[PO#]),CALCULATE(AVERAGE(Table1[Order Amount])))" works perfectly but I can not seem to wrap my head around how it works. Could you provide an explaintation? 
Thanks

Hi.

 

Thanks for your help. I have a question with the same context. Now, when I unpivot comlumns, data is repeated. So, I want to sum the number of values in one column, but it appreas as doubled

Thank you so much.  It worked perfect.

v-yuezhe-msft
Microsoft
Microsoft

@zeckert ,

Do you get expected result when directly creating table visuals as follows? If not, please post you desired result.
1.JPG2.JPG


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mattbrice
Solution Sage
Solution Sage

If your image is a "before" pic, can you include an "after" (or what you want your sample data to look like)?

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!