cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Create a calculated row in a collection

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:

YTDrevenue.png

 

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:

pie.png

 

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

pie2.png

 

Appreciate your help or any suggestion for a better method.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User II
Super User II

@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:

 

apprsomesumremain2379523.png

 

 

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

View solution in original post

Super User III
Super User III

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

RandyHayes_0-1608492090251.png

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

RandyHayes_1-1608492200846.png

No variables or collections needed for this solution.

 

I hope this is helpful for you.

 

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

8 REPLIES 8
Super User II
Super User II

@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:

 

apprsomesumremain2379523.png

 

 

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

View solution in original post

Super User III
Super User III

@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&colon;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.

RandyHayes_0-1608492090251.png

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

RandyHayes_1-1608492200846.png

No variables or collections needed for this solution.

 

I hope this is helpful for you.

 

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

Resolver II
Resolver II

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.

Helper I
Helper I

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 !!

Super User III
Super User III

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

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

@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!

Helper I
Helper I

@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&colon;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>"
)

)

Aditya1728_0-1609240954381.png

 

Super User III
Super User III

@Aditya1728 

Excellent!  Always go for "easy" in PowerApps!

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Top Solution Authors
Top Kudoed Authors
Users online (77,742)