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

Sumifs and Sort Powerapps

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)

 

ItemTotal AmountTargetMultiplier
A50510
A60510
A70510
A8057
B5068
B5068

 

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

 

B52%
A35%

 

I manage to put the Item by grouping them(groupby), but i got stuck on sorting them descending.

 

Appreciate any help.

 

Thanks

Chris

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

@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."

View solution in original post

2 REPLIES 2
Highlighted
Super User III
Super User III

...

Highlighted

@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."

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (9,307)