Forum Discussion
Grouping/Performing Calculations
I am still new to Power BI and was hoping the community has an idea that will help me calculate the results that I am looking for.
I have a long list of items. Some of these items are purposely duplicated and it is the duplicated items that are making my task difficult.
In the table below there are three columns
LENGTH OF ITEMS IN LOCATIONS(days) – the length of days that an item has stayed in a location.
LOCATIONS ON HAND – the number of items in that locations
LENGTH*LOC ONHAND – this column multiplies the two values above
TOTAL LOCATION ON HAND – This value is a SUM of the column “LOCATION ON HAND”
GOAL - To perform the following calculation on each item (a weighted Aging Value😞
AGING = LENGTH*LOC ONHAND / TOTAL OF LOCATION ON HAND
Example - In the above chart, for item 00485-G we would have: 31 / 1933.25 = 0.016
Issue I need help with: In some cases, an item shows up multiple times, as in the example below. I need SUM LENGTH*LOC ONHAND for each item and then divide that by TOTAL LOCATION ON HAND.
The example above would look like this:
(110 + 224) / 1933.25
If I didn’t have duplicate values in the Items column, this would be an easy task but I don’t know how to create a single AGING value for an item that appears more than once. What would be your approach? Thank you in advance for any help you may offer.
Hi Anonymous ,
If you want to achieve this by column,test the below:
sumbygroup = 'Table'[LENGTH OF ITEMS IN LOCATION(days)]*'Table'[LOCATION ON HAND]sumall = sum('Table'[sumbygroup])percent = CALCULATE(SUM('Table'[sumbygroup]),ALLEXCEPT('Table','Table'[ITEM]))/'Table'[sumall]Output result:
And if you want to achieve this by measure ,test the below:
sumgroupmeasure = MAX('Table'[LENGTH OF ITEMS IN LOCATION(days)])*MAX('Table'[LOCATION ON HAND])sumall2 = SUMX(all('Table'),[sumgroupmeasure])per2 = SUMX(FILTER(all('Table'),'Table'[ITEM]=MAX('Table'[ITEM])),'Table'[sumbygroup])/[sumall2]Fianl output:
Did I answer your question? Mark my post as a solution!
Best RegardsLucien
2 Replies
- v-luwang-msft
Community Support
Hi Anonymous ,
If you want to achieve this by column,test the below:
sumbygroup = 'Table'[LENGTH OF ITEMS IN LOCATION(days)]*'Table'[LOCATION ON HAND]sumall = sum('Table'[sumbygroup])percent = CALCULATE(SUM('Table'[sumbygroup]),ALLEXCEPT('Table','Table'[ITEM]))/'Table'[sumall]Output result:
And if you want to achieve this by measure ,test the below:
sumgroupmeasure = MAX('Table'[LENGTH OF ITEMS IN LOCATION(days)])*MAX('Table'[LOCATION ON HAND])sumall2 = SUMX(all('Table'),[sumgroupmeasure])per2 = SUMX(FILTER(all('Table'),'Table'[ITEM]=MAX('Table'[ITEM])),'Table'[sumbygroup])/[sumall2]Fianl output:
Did I answer your question? Mark my post as a solution!
Best RegardsLucien