Lightning Experience Reports & Dashboards Specialist - Challenge 5 - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Jacobo SánchezJacobo Sánchez 

Lightning Experience Reports & Dashboards Specialist - Challenge 5

Hi All,

I'm having the following warning when trying to check the Lightning Experience Reports & Dashboards Specialist - Challenge 5:

"Challenge Not yet complete... here's what's wrong: 
We can’t find the correct fields on the 'SolarBot Loyalty Revenue' report, or data isn't organized by Stage."

although the report is organized by Stage (first Stage, then Account) and seems to have all the requested fields. Bellow, there's a capture of the report

SolarBot Loyalty Report

Any help would be appreciated.
Best Answer chosen by Jacobo Sánchez
Nicole PomponioNicole Pomponio
I am stuck on the same but with a different error. From my understanding, it can only be grouped by Stage

All Answers

Nicole PomponioNicole Pomponio
I am stuck on the same but with a different error. From my understanding, it can only be grouped by Stage
This was selected as the best answer
Jacobo SánchezJacobo Sánchez
Hi Nicole,

I took off the Account grouping and it worked. Kind of weird because that was my first approach to the challenge and I was having other warnings then. Besides, as the requirement says "This column should show a currency value 10% higher at the Subtotal and Total levels if the account has at least one SolarBot, and the original value if not." you may think that they are requesting the Adjusted Expected Revenue for each and every Account. 

Anyway, thanks for your reply, I'm heading for Challenge #6 :)
Jacobo SánchezJacobo Sánchez
I had that error too. I might be because of the Custom Formula you are using. I tried several different approaches for the formula. The one that worked for me is this:

IF(Account.Number_of_SolarBots__c:SUM < 1, EXP_AMOUNT:SUM, EXP_AMOUNT:SUM + EXP_AMOUNT:SUM * 0.1)
Vaibhav PurohitVaibhav Purohit
Hi Jacobo,

I need some help.
I'm done with the formula part but i get an error about the chart. It goes : 
We can’t find a graph for the 'SolarBot Loyalty Revenue' report, or it doesn't display the stage, expected revenue, and adjusted expected revenue as required.

While i added a vertical bar graph to the report 
User-added image

How do i get two report values against stage in graph.
Any help!
Jacobo SánchezJacobo Sánchez
Hi Vaibhav,

you need to add a measure (+Measure) on the Y-axis. Check the image below.

User-added image

Hope that helps.

Cheers,
 
Vaibhav PurohitVaibhav Purohit
Thanks Jacobo, that was the exact issue.
Kate SpryKate Spry
My report and formula look the same as above (with no account grouping), but I'm getting this error on challeng 5: The settings for the 'Adjusted Expected Revenue' column aren't correct.  When I try to select (+Measures) I can compare, but it does not save since it says I must choose measures of matching data types.  

What am I missing?
Somya SrivastavaSomya Srivastava
Hi,

Change the format of your Formula to Currency. It will work then.
Benjamin ThomasBenjamin Thomas
Hi,

I've got the following error message User-added image
I created the report as followed with the Adjusted Expected Revenue column with the formula : IF(Account.Number_of_SolarBots__c:SUM < 1, EXP_AMOUNT:SUM, EXP_AMOUNT:SUM + EXP_AMOUNT:SUM * 0.1).
User-added image
Any idea what's wrong here ?
Thanks.
Somya SrivastavaSomya Srivastava
Hi Benjamin, 

Try with this formula:

IF(Account.Number_of_Solarbots__c:SUM>0, EXP_AMOUNT:SUM*1.1, EXP_AMOUNT:SUM)

I hope this solves your problem.
Benjamin ThomasBenjamin Thomas
Thanks Somya, I passed the challenge. :)
Meradith StretzMeradith Stretz
Thank you Somya! and Benjamin for posting
Sam WendtSam Wendt
Hey Everyone -- I'm getting the same error: "We can’t find the correct fields on the 'SolarBot Loyalty Revenue' report, or data isn't organized by Stage."

I've reviewed every possible reason for the error:
1. Grouping by more than just Stage (like adding Account)
2. Having the wrong formula for Adjusted Expected Revenue (AER)
3. Having unnecessary columns
4. Having the format of AER be Number or Percent instead of Currency with 2 decimal points.

I've made sure I correctly did all these things, yet I'm still getting the error. Here's my report: User-added image

Can someone help point out to me what I'm missing?
Sam WendtSam Wendt
CORRECTION: For some reason, I had #1 on my report. So I moved the Account down from where it was under GROUP ROWS and moved it down to DETAIL COLUMNS and I passed this challenge.
Frank VerrillFrank Verrill
My report type is Opportunity and saved in the SolarBot Executive Reports but I get the followng error:
Challenge Not yet complete... here's what's wrong: 
We can’t find a report with name 'SolarBot Loyalty Revenue' in the correct folder, or it uses an incorrect report type.
The report and formula are formatted correctly.
Any ideas?
Somya SrivastavaSomya Srivastava
Hi Frank,

Please check with the toggles at the bottom. Keep the row count toggle disabled, and the rest detail rows, subtotals and grand total enabled.

Also, make sure you have the required columns in your report including the Adjusted Expected Revenue
formula column too, and grouped by Stage.
 
Nyjil JoshiNyjil Joshi
My issues was adjusted expected revienue not in currency data type. :)
Seyran MammadovSeyran Mammadov
Hi Frank! 

Make sure that both your report and chart are called "SolarBot Loyalty Revenue"
Kevin Garcia ChaviraKevin Garcia Chavira
Hi!! Someone knows why I dont have the "+Measure" option??User-added image
Damian BDamian B
Hi. Looking for some advice on the adjusted expected revenue formula. I have passed the challenge, but the formula does not display the correct results. E.G when there is an account without a SolarBot, the formula is still adding 10% at the subtotal level? Am I missing something and how would one adjust the formula to give the correct result of $133,086.48 with the report as currently constructed.
Constructing the report with type as a row grouping would fix the issue it seems. 
Would like the badge to be updated to guide towards the correct answer as you would have pretty unhappy executives if you overstated all the non solarbots :) 
solarbot screen shot
 
Alex ChenAlex Chen
Hi Somya Srivastava,

Great, i have same issue and it solves my problem.

"Change the format of your Formula to Currency. It will work then."

Thanks.
 
Ragavi RajaganesanRagavi Rajaganesan
Hi Kevin

You should add sum in expected revenue column, If it doesn't works then delete everything and create it again. It works for me.
Richard CopelandRichard Copeland

     I am also on Step 5. I am getting the error "We can’t find a report with name 'SolarBot Loyalty Revenue' in the correct folder, or it uses an incorrect report type." I used the Opportunities report type. I have my report saved by the name "SolarBot Loyalty Revenue" in the SolarBot Executive Reports folder. It's grouped by Stage and the Chart shows the Sum of Expected Revenue and the formula column Adjusted Expected Revenue side by side with Stage along the bottom. The report works fine and the chart looks good. I turned off Row Counts at the bottom, but left Detail Rows, Subtotals & Grand Totals on. My formula appears to be correct and Validates.

IF (Account.Number_of_SolarBots__c:SUM >=1, EXP_AMOUNT:SUM*1.1, EXP_AMOUNT:SUM)

     Should I have used a different Report Type than Opportunities? Any hints or clues would be greatly appreciated. Thanks.
- Richard

Richard CopelandRichard Copeland
     Ignore my question. This is bizarre. I closed my playground and then closed my browser. I opened the browser and logged in and opened the Challenge. I launched my playground and went back to the challenge tab and clicked on "Check Challenge" and it passed. With no modifications. :)
Caroline MengCaroline Meng
I met exactly the same situation like Richard. then I closed the browser and went to lunch. now I opened the browser and check the challenge. it passed!
Prajeesh KaratPrajeesh Karat
Hello Guys,
I am also stuck in the same place as of Kate Spry. I am getting "Choose Measure with matching data type" I have already used the "Adjusted Expected  Revenue" formula field as Currency field. While giving +measure, i do not see any field other than Record Count and Adjusted Expected Revenue in the drop down menu. Below is the formula i have used(It's same as that of @SomyaSrivastava )
IF(Account.Number_of_SolarBots__c:SUM > 0, EXP_AMOUNT:SUM * 1.1, EXP_AMOUNT:SUM)

Kindly let me know if i have missed anything.

Regards,
Prajeesh
Prajeesh KaratPrajeesh Karat
Guys.. Able to figure out.. I had not "SUMMARIZE" the Expected Revenue field to SUM. Once clicked on it.. The issue got resolved. :) 
Doug ContaoiDoug Contaoi
Hi Guys!  I don't know if I should piggyback my question/comment in this thread or start a new one.  Anyhow, I appreciate the community and finding answers to general questions and challenges as I prepare for the SF Admin exam.  My question is this, I had a general idea that this challange would require a special formula, but without a background, how would I know how to create a fomula without knowing how to write one?  How do you suggest I learn this once I apply SF in the real world or increase my knowledge in this technical aspect?  Thanks everyone!
Lucy EdwardsLucy Edwards
Hello, I am getting the error message:

"We can’t find a graph for the 'SolarBot Loyalty Revenue' report, or it doesn't display the stage, expected revenue, and adjusted expected revenue as required."

Here is my report/chart. What am I doing wrong?

User-added image
Lucy EdwardsLucy Edwards
Hello could anyone help me with the above? Thank you
 
Leonard CarrilloLeonard Carrillo
Hi Lucy, 
You need to go into Chart Properties (cog) and change from Bar to Column format. This puts Stage at the bottom.
Smita HodiggeriSmita Hodiggeri
@Leaonard -- Thank you. this helped me resolve my issue.
Anthony OkolieAnthony Okolie
Hi everyone,
       How do I change the Adjusted Expected Revenue to a currency data type? There is no currency option to select. Secondly, do I need to summarize by the sum of Adjusted Expected Revenue? When I do this, it appears in the options I can select as the Y-axis value but as sum of the values. But here, I am seeing 'Adjusted Expected Revenue' not the sum. Thanks for your help

Best regards,
Anthony
Amanda Dean SingletonAmanda Dean Singleton

Amanda Singleton
Hi Everyone. I really struggled on this one, but have since gotten it to check out properly. The answer does not answer the actual question. In the question set up, the analyst states that 'opportunities are 10% more likely to close successfully.' This is not the same as expected revenue increasing by 10%.
The actual formual should be 
if((Account.Number_of_SolarBots__c:SUM>1),((PROBABILITY:SUM+10%)*AMOUNT:SUM),EXP_AMOUNT:SUM))
jelena heylandjelena heyland
Hi Amanda, I thought this too, but the task clearly says to incleas the currency value of the expected revenue by 10% - so even if the first logical thought is different, that is not the answer. 
Ranil GoonesekeraRanil Goonesekera
Lightning Experience Reports & Dashboards Specialist - Challenge 5 Issue

Hello All,
I have been stuck for days with completing challenge five of Reports and Dashboard specialist.  I have checked the forum for my perticular issue with no success.  It is time to ask for an additional set of eyes.  Can someone please take a look?
Note:  The system won't let me name the Adjusted Expected Revenue by that name so, I dropped the last e.  Not sure if that has an effect.  (I have requested the Trailhead Team to clear cache)
User-added image
User-added image
 
jelena heylandjelena heyland
The name has to be exactly what it says it should be. I redid the report when I had that exact problem. I have started to first naming them wrongly now, so I can rename them correctly when I know what I did is correct. Where do you ask to have cache cleared? The graph looks good so far, try it with the correct name. sometimes the refreshing has also not been done, or the saving. Sometimes logging off and back in is the best solution.  
 
Ranil GoonesekeraRanil Goonesekera
@Jelena Heyland  Phew.. thanks to you I cleared challenge #5.  After reading what you said, something came to my mind.  I will write it so that others don't have to bang their heads for days.  I correctly named the column first time around but did something else wrong and couldn't clear the challenge.  As a step for fixing the issue I tried deleting the summary formula and tried to redo it from scratch.  That is where I ran into the issue of naming.  Although I deleted the formula and I can no longer see the Adjusted Expected Revenue column, system hangs on to the data. That is why I couldn't redo the formula and give it the same name.  Light bulb went off after reading your response.  Rather than creating the formula from scratch, click on the Add column... field like you normally do and you will see the original copies of the formula you deleted still in the system.  Click the one with the correct name and edit that rather than create one from scratch.  Thanks again!
jelena heylandjelena heyland
oh wow, i wish i had had that idea! I had to redo it from scratch, because i could not change the setting from number to currency. I am so glad I could help! 
vineetha csvineetha cs
I got the error even i did everything correctly -"We can’t find a graph for the 'SolarBot Loyalty Revenue' report, or it doesn't display the stage, expected revenue, and adjusted expected revenue as required." 

I solved it by the below simple steps

1) Select Edit option
2)Save and run the report
3)Now check the challenge again 
Vinod KumarVinod Kumar
My chart does not have Expected Revenue, also chart does not have +measure option. Any Idea what i am missing? 

User-added image
Vinod KumarVinod Kumar
I was missing sum of Expected Revenue field.
User-added image
Gennady YanovskyGennady Yanovsky
To help out all the poor souls trying this challange and to save time for everyone ( after me being stuck on this for 2 days ) here are the answers:

Opportunity Report - SolarBot Loyalty Revenue - Saved in SolarBot Executive Reports

In the Report under GROUP ROWS - you should ONLY have Stage

In the Report under Columns - you should have the following Opportunity Information Objects:
Account Name
Opportunity Name
Type
# Probablilty (%) - click and Summarized by Sum
# Amount - click and Summarized by Sum
# Expected Revenue - Sumarized by Sum

Create a Summary Formula - ( look for Columns in the Outline Seciton and click on the down arrow ) - call it - Adjusted Expected Revenue:

Here is the actual formula (make sure to validate) - IF(Account.Number_of_SolarBots__c:SUM > 0, EXP_AMOUNT:SUM*1.1, EXP_AMOUNT:SUM)

Add a column Chart with the following Criteria: 

Chart Options

Follow this screenshot to make sure you made the right look of the report to verify all that was mentioned above.

Report Layout

Also if this was helpful, please rate it to help others.
Sachin ChotheSachin Chothe
Thank you Gennady Yanovsky
Somya GuptaSomya Gupta
Hi,

I am not getting the option to select Expected Revenue in Y-axis dropdown list. 
User-added image
Getting the error message: 
Challenge Not yet complete... here's what's wrong: 
We can’t find a graph for the 'SolarBot Loyalty Revenue' report, or it doesn't display the stage, expected revenue, and adjusted expected revenue as required.
Jordan SchwebachJordan Schwebach
I was able to complete this challenge using recommendations above, but absolutely disagree with what Trailhead accepts as the "correct" answer.
  1. The problem states that opportunities with SolarBots are 10% more likely to close (read: Probability +10%). So, Adjusted Revenue should be increased by 10% of Amount, not 10% of Expected Revenue. And yet, the accepted formula uses "EXP_AMOUNT:SUM*1.1". The correct formula should be something using Amount*(Probability + 0.1). Even correcting this, though, will still be incorrect because...
  2. The Summary Formula column is applied to the subtotal, not individual rows. This means if only one opportunity in a group has a SolarBot, the formula is still calculating revenue for ALL opportunities in that group. Try it, go delete SolarBots from Accounts and the numbers will not change. So, we can just use Row-Level formula instead, right?
  3. NO! The Row-Level formula can't output as a Currency format, which means it can't appear in a chart alongside Expected Revenue; they must be the same format. But at least the numbers are correct now..?
  4. Almost: Closed Won opportunities (and maybe Closed Lost, depending on the narrative we want to go for) should be excluded with report filters or in the adjusted formula. The assumption we were given is that SolarBot ownership changes Probability, which is limited to 100%. An Opportunity won't magically make 10% more money once it hits the Closed Won stage.
Anyone feel free to correct me on any points. Honestly I think I've learned more figuring out where this challenge was wrong than I would have otherwise, so well played Salesforce. 
 
Claire LewisClaire Lewis
I didn't realize I had two verisons of the report saved.  When I deleted the older one, I passed
Pedro CascalhoPedro Cascalho
@Jordan Schwebach came here for your observation. They should correct the exercise:)
@Damian B also referred the same above.
Would actually like to know how to apply the graphic with the use of a Row-Level formula instead.
Patrick FallonPatrick Fallon
@jordan Schwebach - Was going to type out the same frustration. The math is incorrect on this. It's adding 10% to those without solarbots because one account in the stage has 1 or more solarbots. I kept receiving the error despite the original build having the correct data. The way to truly achieve the correct Adjusted Expected Revenue would be to group by Stage and then Account Name. Alas, we are stuck with SF rules, so have to figure out what they will accept to move along. Cheers all!
Paul WilliamsPaul Williams
Can someone (maybe from Salesforce) explain why this formula works: IF(Account.Number_of_SolarBots__c:SUM < 1, EXP_AMOUNT:SUM, EXP_AMOUNT:SUM + EXP_AMOUNT:SUM * 0.1)

and this one does not: IF(Account.Number_of_SolarBots__c:SUM > 0, EXP_AMOUNT:SUM+(EXP_AMOUNT:SUM*00.10), EXP_AMOUNT:SUM)

They are the same and from what I can see all of my subtotals and totals were the same with each. Definitely looks to be a glitch in the SuperBadge.
Yun WuYun Wu

Can someone please tell me whaht is wrong with my solution.

Here is the error 
User-added imageHere is my graph:

User-added imageHere is my table:

User-added imageThank you very much.

Yun WuYun Wu
I figured out what was the problem.  For whatever the reason I have two copies of he same report and one of them doesn't have a graph. I delected one and I passed.
James MacfarlaneJames Macfarlane
Hi
@Damian B
@Jordan Schwebach
@Pedro Cascalho
@Patrick Fallon


RE: Report and graph showing a 10% increase for Adjusted Expected Revenue even though some of the customers have no SolarBots.

I am pretty dismayed that a solution with incorrect maths is accepted as the correct answer for a learning exercise. However, I did learn more than I otherwise would have.

Grouping by Stage and then Account Name doesn’t work as if any customer for the Stage has a SolarBot, the Adjusted Expected Revenue for the Stage shows an increase of 10%.

I think I have found a (rather convoluted) way to show the correct Adjusted Expected Revenue. Set up a Row-Level Formula “Adjusted Expected Revenue” with a formula of:
if (Account.Number_of_SolarBots__c > 0, EXP_AMOUNT * 1.1, EXP_AMOUNT)

To get around not being able to set the Row-Level Formula output type to “currency”, set up a Summary Formula “Expected Revenue Sum (number)”:
Formula: EXP_AMOUNT:SUM
Formula Output Type: Number

“Sum of Adjusted Expected Revenue” and “Expected Revenue Sum (number)” can then be used together for the column chart.

User-added image

The overall Adjusted Expected Revenue now shows an increase of 5.91% rather than 10%.

Regards
James
Gurinder bhachuGurinder bhachu
Summary of this problem:- 
  1. Group Rows
    1. Stage ( Only Stage, no other field)
  2. Columns
    1.  Opportunity Name
    2. Account name 
    3. Type
    4. Amount (Sum)
    5. Expected Revenue (Sum)
    6. Probability   (Sum)
  3. SUMMARY FORMULA COLUMNS
    1. Formula Output Type = Currency
    2. IF(Account.Number_of_SolarBots__c:SUM >0, EXP_AMOUNT:SUM * 1.1, EXP_AMOUNT:SUM )
  4. Add Graph Horizontally 
    1. Chart Title= SolarBot Loyalty Revenue
    2. X-Axis = Stage
    3. Y-Axis = Adjusted Expected Revenue
    4. Show Values
Please mark as Best Answer if this works for you. 
Thanks you 
 
Brad WallaceBrad Wallace

I was able to successfully pass this step using everyone's comments.  Thank you.

However, I'd like to note the appropriate method to provide the correct value, in real life, would be to use a Row Level Formula.  The Row Level Formula will apply the logic to each row, which is what was requested.  The Summary Level Formula does not apply the logic to each row.

Real Life Value for Adjusted Expected Revenue

Gary PinderGary Pinder
I agree with you on that Brad.  My report was looking great having grouped by Stage and then by Account Name.  this prevented Accounts with ZERO SolarBots from being uplifted by the 10%.  The accepted solution by the Trailhead takes everything in the Stage 'group' and applies 10% to it. 
Anthony RamosAnthony Ramos
Thanks everyone with all the help!

The part that tripped me up was the Row-Level Forumula vs. Summary-Level Forumla.
Be sure to use the Summary-Level Formula when entering in the IF statement.

Hope this helps!

User-added image
Brandon GreshamBrandon Gresham
@Gennady Yanovsky : thanks so much for your detailed answer! I mostly had the right thing by the time I'd gotten to yours but still couldn't get mine to pass -- turns out, I had mistakenly included "Account Type" instead of "(Opportunity) Type". Your list & graph to compare to mine was very useful to spot that error.
Meraj KhanMeraj Khan
Summary FOrmula for Challenge 5
SPRRUTIRAJ PANDASPRRUTIRAJ PANDA
Hi All 
I try all the above solution but not solved this challange .
And getting same error __
We can’t find a report with name 'SolarBot Loyalty Revenue' in the correct folder, or it uses an incorrect report type.
Close errors.
 for your reference please see the pic .
 also i try this 
Opportunity Name
Account name 
Type
Amount (Sum)
Expected Revenue (Sum)
Probability   (Sum)User-added imageUser-added imageUser-added image