Create a calculated row in a collection

[Aditya1728]

Hi Experts,

I have created a collection which generates a single column single row. 

ClearCollect(YTDrevenuePercentage,((Sum (YTDrevenue, 'Expected Number')/(30))*100))

Something like this:

I want to generate a pie chart but for that I need another row in that collection as (100-83.333333 = 16.66666666)

The reason being that the pie chart should look something like this:

Once I get this, I will place a circle on top of it, to show something like below in the dashboard:

Appreciate your help or any suggestion for a better method.

[poweractivate · Accepted solution]

@Aditya1728 

Try using Variables to be able to manipulate the one sum from another variable to do 100-SomeSumVariable.

For example:

Set(_SomeSum,83.333);Set(_SomeSumRemain,100-_SomeSum);ClearCollect(_CollectionYTDrevenuePercentage,{Value:_SomeSum},{Value:_SomeSumRemain})

(in your case, instead of Set(_SomeSum,83.333) you would do Set(_SomeSum,YourComplexFormulaHere)

Which looks something like this:

In case you want to try it yourself:

First, save the attached msapp SomeSumApp2895023-v1.msapp to local computer - then:

1.  Navigate with your browser manually to this URL (or just click the following link): https://us.create.powerapps.com/studio/
2.  Then, Click "Open" on the left side
3.  Next, Click "Browse" on the right side
4. Finally, select the msapp file from the window when prompted.
5. Done

[RandyHayes · Accepted solution]

@Aditya1728 

You might consider the following instead since your end result appears to at least contain 3 controls (a chart, a label and a circle).

Add an Image control to your screen and set the Image property to the following:

With({chartVals:  ((Sum (YTDrevenue, 'Expected Number')/(30))*100)},

    "data:image/svg+xml," &
    EncodeUrl("<svg width='100%' height='100%' viewBox='0 0 42 42' xmlns='http://www.w3.org/2000/svg'>
        <circle class='donut-hole' cx='21' cy='21' r='15.91549430918954' fill='#fff'></circle>
        <circle class='donut-ring' cx='21' cy='21' r='15.91549430918954' fill='transparent' stroke='lightgray' stroke-width='3'></circle>
        <circle class='donut-segment' cx='21' cy='21' r='15.91549430918954' fill='transparent' stroke='maroon' stroke-width='3' stroke-dasharray='" & Text(100-chartVals) & " " & Text(chartVals) & "' stroke-dashoffset='21'></circle>
        <text x='13' y='25' fill='red' font-size='8pt'>" & Text(100-chartVals, "[$-en-US]0%") & "</text>
        </svg>"
    )

)

NOTE: this forum has a tendency to alter these type of formulas because of some of the text in it, so here is an image of the above formula in case it ends up getting altered.

This is what the image will look like (obviously depending on the value you have):

No variables or collections needed for this solution.

I hope this is helpful for you.

[Anonymous]

I wouldn't use collections either.

Just to keep it simple, I would use an expression like the one below on the Items property of the pie chart, but maybe I'm missing something.

[(Sum(YTDrevenue, 'Expected Number')/30),(1-(Sum(YTDrevenue, 'Expected Number')/30))]

You can use variables too instead of the whole expression.

[Aditya1728]

Thanks @poweractivate , it worked like charm...
Need some more help but I will keep it for a different post after trying few more things..
But the variables were helpful it creating that extra calculated row !!

[RandyHayes]

@Aditya1728 

You might still want to consider the image solution I provided you.  It's far easier to implement in total for your chart.  Variables are highly overused and you need not use any for your solution.  Just an extra 2 cents to add.

[Aditya1728]

@RandyHayes 

I will try it and update you likewise.....want to find a way to avoid hardcode variable values, once I am able to resolve that, will surely give a try to your solution!

[Aditya1728]

@RandyHayes 
I tried the image control you suggested and it is working as expected. And I am going with your solution because it avoids creation of any collection and is easier to maintain.
Thank you, made a small modification to the way i needed it and this is what I get now 🙂

I changed some colors and offsets:

With({chartVals:(((Sum('YTD','Revenue generated'))/(15)*100))},

"data:image/svg+xml," &
EncodeUrl("<svg width='100%' height='100%' viewBox='0 0 42 42' xmlns='http://www.w3.org/2000/svg'>
<circle class='donut-hole' cx='21' cy='21' r='15.91549430918954' fill='RGBA(241, 244, 249, 249)'></circle>
<circle class='donut-ring' cx='21' cy='21' r='15.91549430918954' fill='transparent' stroke='RGBA(127, 178, 57, 1)' stroke-width='3'></circle>
<circle class='donut-segment' cx='21' cy='21' r='15.91549430918954' fill='transparent' stroke='RGBA(184, 0, 0, 1)' stroke-width='3' stroke-dasharray='" & Text(100-chartVals) & " " & Text(chartVals) & "' stroke-dashoffset='0'></circle>
<text x='15' y='23' fill='RGBA(0, 0, 0, 1)' font-size='6pt'>" & Text(chartVals, "[$-en-US]0%") & "</text>
</svg>"
)

)

[RandyHayes]

@Aditya1728 

Excellent!  Always go for "easy" in PowerApps!

[Aditya1728]

@RandyHayes 

Hi Randy , a follow up question on this.

The donut image when updates the calculation does not always start from 12 O'clock.

Something like this:

When the result is 25% then it shows perfectly fine but when it is anything else, then you see the green part changes its starting position.

Is there a way it always starts from 12 O'clock and then shows progress from there?

Appreciate your help.