Sumifs and Sort Powerapps

[pamboys09]

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

[mdevaney]

...

[mdevaney · Accepted 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."