Accursed PARENTGROUPVAL function - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Alice LeAlice Le 

Accursed PARENTGROUPVAL function

Please help, I love/hate this function so much...!

(Anyone else having issues posting screenshots or links to Answers posts right now?! My entire browser greys out if i click either button, which will make describing this fun...)

I have a matrix report with Provider as my row grouping, Date as my column grouping, and summarizing a field called "# of Devices". I'd like to show the % of devices each Provider has each week, ie the % of the grand summary for that column. I cannot for the life of me configure this properly to do so. 

Here is my current formula: 
Devices_total__c:SUM / PARENTGROUPVAL(Devices_total__c:SUM, Provider__c, Date__c)

That provides me 100% as the result on every row... not exactly helpful :)  For "Display" I have Specific Groups selected, Group Group = Provider and Column Group = Date. If I change that to "All SUmmary Levels" I get the error "You must select a grouping context to use any report summary function." If i change either of the specific groups to "Grand Total Only" I get the error "Incorrect argument type for function 'PARENTGROUPVAL()'." 

What do i need to adjust here? 

Best Answer chosen by Alice Le
Steve MolisSteve Molis
Thanks, so basically this

Formula => 

Opportunity.How_much_beer_do_you_owe_SteveMo_Number__c:SUM 
 / 
PARENTGROUPVAL(Opportunity.How_much_beer_do_you_owe_SteveMo_Number__c:SUM, ROW_GRAND_SUMMARY, CLOSE_DATE) 


Screenshot =>
https://trailblazers.salesforce.com/0694S000001YyWzQAK

All Answers

Steve MolisSteve Molis
The image, link, and code buttons in the Toolbar are broken (Salesforce is still working on it) so you'll need to upload the file to your Trailblazer Profile or GoogleDocs, or something like that, and post a link to the image file (as text) 
Alice LeAlice Le

Ah thank you SteveMo!

OK, view of the report currently (with 100% on each row instead of the % of total): https://imgur.com/C8UXrdq 

Display settings: https://imgur.com/B2GYWMV

And the bad formula resulting in screenshot #1, % of itself intead of % of total: 
Devices_total__c:SUM / PARENTGROUPVAL(Devices_total__c:SUM, Provider__c, Date__c

Steve MolisSteve Molis
Okay, so in A1 do you want 

402 / 95,354 

or

402 / (401 + 454 + 476 + 467)
Alice LeAlice Le
The first one - 402 / 95,354. 
Steve MolisSteve Molis
Thanks, so basically this

Formula => 

Opportunity.How_much_beer_do_you_owe_SteveMo_Number__c:SUM 
 / 
PARENTGROUPVAL(Opportunity.How_much_beer_do_you_owe_SteveMo_Number__c:SUM, ROW_GRAND_SUMMARY, CLOSE_DATE) 


Screenshot =>
https://trailblazers.salesforce.com/0694S000001YyWzQAK
This was selected as the best answer
Alice LeAlice Le
AAHHHH THANK YOU!! Works perfectly!

OK, one more question if I may (and then I'll have to change the formula back so that the value of beer I owe you is in fact 100% at all times): when i add a chart to this report, I can do exactly what I'd hoped: graph relative % of beer per account, week over week: https://imgur.com/4NQvhoZ

..but when I add the report to a dashboard, even if I check "use chart settings from report," I cannot group the X axis by date; it errors out and insists i group first by Account: https://imgur.com/zM34DCq 

It will not provide my beautiful new "% of Ecosystem" field as an available Y axis value, unless I group the X axis by Account first (which makes for a very confusing chart): https://imgur.com/oOuTCLc

Why would this chart/visualization be available on the report but error out when used on a dashboard?
Steve MolisSteve Molis
Okay, lemme play around with it in my Dev org
Steve MolisSteve Molis
I notice that your Report Chart is a Line Chart and the Dashboard component is a Stacked Bar Chart
Alice LeAlice Le
That's what it defaulted to when I used the "add to dashboard" button directly from the report, just to make sure I wasn't losing something in translation when I added the report to the dashboard separately. So even though the chart on the report is a Line Chart, when I click "add to dashboard" it shows up as a stacked bar, with the "use chart settings from report" button checked... but also with the error on the Y axis telling me to plot Account then Date. I am stymied. 
Steve MolisSteve Molis
What happens when you try to add it as a new Component the old fashioned way?
Alice LeAlice Le
Then I get that third pic (https://imgur.com/oOuTCLc) - the summary formula field is not available as a choice on the Y axis (...unless I switch the X axis to be grouped by Account) :( 
Steve MolisSteve Molis
Try this 

https://trailblazers.salesforce.com/0694S000001YzLEQA0
Steve MolisSteve Molis
Does it have to be a Line Chart?
Steve MolisSteve Molis
It still seems to work for me

https://trailblazers.salesforce.com/0694S000001YzM2QAK 

Can you post a complete screenshot of the Report?  

Are you positive that the Dashboard is pointed at the same report, and not another version of it?  

Did you Save the Report?
Alice LeAlice Le
In the screenshot you have, are you able to swap Account and Date though, so that Date is the primary X axis measure? This is what i'm trying to get, where each line is an account: https://imgur.com/a/NJUF1bU 
Steve MolisSteve Molis
Okay I dunno why, and there's gotta be a better wat to do it, but I was able to get it to work by changing it to a Summary Report =>  https://trailblazers.salesforce.com/0694S000001YzNUQA0


and using this Formula 

Opportunity.How_much_beer_do_you_owe_SteveMo_Number__c:SUM 
 / 
PARENTGROUPVAL(Opportunity.How_much_beer_do_you_owe_SteveMo_Number__c:SUM , CLOSE_DATE) 
 
Alice LeAlice Le
HUZZAH! ALL THE BEERS TO YOU! I don't know that I ever would have done that / figured that out... THANK YOU!