Hello,
I've been testing powerapps and I got stuck on trying to summarize the report to a gallery.
So i have this data on sharepoint list. (*dummy data)
Item | Total Amount | Target | Multiplier |
A | 50 | 5 | 10 |
A | 60 | 5 | 10 |
A | 70 | 5 | 10 |
A | 80 | 5 | 7 |
B | 50 | 6 | 8 |
B | 50 | 6 | 8 |
I want to show on my gallery group by item and amount % sort by the highest on top
Amount % = Total Amount / (Target * Multiplier)
Here's the expected result in Gallery
B | 52% |
A | 35% |
I manage to put the Item by grouping them(groupby), but i got stuck on sorting them descending.
Appreciate any help.
Thanks
Chris
Solved! Go to Solution.
@pamboys09
Like this...
// your data
ClearCollect(colDummyData,
{Item:"A",'Total Amount':50,Target: 5,Multiplier: 10},
{Item:"A",'Total Amount':60,Target: 5,Multiplier: 10},
{Item:"A",'Total Amount':70,Target: 5,Multiplier: 10},
{Item:"A",'Total Amount':80,Target: 5,Multiplier: 7},
{Item:"B",'Total Amount':50,Target: 6,Multiplier: 8},
{Item:"B",'Total Amount':50,Target: 6,Multiplier: 8}
);
// calculate percentage for each item and group
ClearCollect(
myUngroupedSolution,
DropColumns(
AddColumns(
GroupBy(AddColumns(colDummyData, "Amount %", 'Total Amount' / (Target * Multiplier)),"Item","GroupedItems"),
"Sum of Value",Sum(GroupedItems,'Amount %')
),
"GroupedItems"
)
);
// find the proportion of the whole and sort
ClearCollect(
myGroupedSolution,
ShowColumns(
Sort(
AddColumns(
myUngroupedSolution,
"Percentage",
Text('Sum of Value'/Sum(myUngroupedSolution,'Sum of Value')*100,"[$-en-US]0.0%")
),Percentage, Descending),
"Item","Percentage")
);
---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."
...
@pamboys09
Like this...
// your data
ClearCollect(colDummyData,
{Item:"A",'Total Amount':50,Target: 5,Multiplier: 10},
{Item:"A",'Total Amount':60,Target: 5,Multiplier: 10},
{Item:"A",'Total Amount':70,Target: 5,Multiplier: 10},
{Item:"A",'Total Amount':80,Target: 5,Multiplier: 7},
{Item:"B",'Total Amount':50,Target: 6,Multiplier: 8},
{Item:"B",'Total Amount':50,Target: 6,Multiplier: 8}
);
// calculate percentage for each item and group
ClearCollect(
myUngroupedSolution,
DropColumns(
AddColumns(
GroupBy(AddColumns(colDummyData, "Amount %", 'Total Amount' / (Target * Multiplier)),"Item","GroupedItems"),
"Sum of Value",Sum(GroupedItems,'Amount %')
),
"GroupedItems"
)
);
// find the proportion of the whole and sort
ClearCollect(
myGroupedSolution,
ShowColumns(
Sort(
AddColumns(
myUngroupedSolution,
"Percentage",
Text('Sum of Value'/Sum(myUngroupedSolution,'Sum of Value')*100,"[$-en-US]0.0%")
),Percentage, Descending),
"Item","Percentage")
);
---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."
Stay up tp date on the latest blogs and activities in the community News & Announcements.
Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST
Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.
User | Count |
---|---|
198 | |
173 | |
62 | |
33 | |
32 |
User | Count |
---|---|
339 | |
271 | |
105 | |
71 | |
58 |