Nested Groups behaving erratically

[Anonymous]

Hello all,

I am having trouble implementing nested groups.

I have a data cube that I am drawing car insurance data from. This data is sorted in my report by the city and state in which the claimant resides. (Both fields are listed in the cube under the Claimant category.) I have created a nested row group where City is under State (City is shown as Details2 in the Designer view).

The problem is that no matter how I group the State field, I end up with bad results.

The best result I got was when I grouped by City. When I did that, I got results that alternated between State and City rows, looking like this:

Now I only say this is the best because in this case, 4100 is the sum of the column it is under. (There is actually 3x more data in this report than I am showing here, but I just gave you this as a sample.) If I sort by any other variable, I get similar results but the numbers do not add up to 4100.

For example, if I group by Direct_Claim_Deductible_Recoverable_End_of_Year, I get this result:

:

This one groups the cities under the states and allows me to expand and contract the state rows, but there are repeat state rows, the data bar is messed up, and the numbers do not add up to the total. (I am showing the whole report here)

Trying to send the State field without grouping does not work and gives me this error:

I also noticed that each report has 17 pages, but they only show the data table taking up one page. This is true regardless of how much data is actually in the table, mind you.

I want all of this:

• I want to group the cities by state and be able to expand and contract them, but I also want the numbers to add up to the total at the bottom.
• I would also like to get the data bars to show a percentage of the total -- for the cities, a percentage of the state total, and for the states, a percentage of the whole total.
• And I would like to eliminate the extra blank pages at the end of the report.

Any actually helpful answers would be greatly appreciated. (Seriously, I have received some most unhelpful answers in the past, so please actually read the whole post before responding.)

If you could help me with even one of the problems listed above, I would be grateful.

EDIT: I have figured out why the cities were showing up automatically: I had the Visibility set to "Show" instead of "Hide". Still doesn't explain what's going on with the State rows though.

[v-xiaoyan-msft]

Hi  @Anonymous ,

Have you read this blog?

How to create a support ticket in Power BI - Microsoft Power BI Community

When you get to this support page, you should be clicking on get support at the bottom of the page, then searching for what you need in the search box, and if no suitable answer appears, you can click on continue to get support.

Also, the same post will be merged in the forum, and in addition to forum support, other active users who have ideas will also come to help you solve the problem.

 

Best Regards,
Community Support Team _ Caitlyn

[Anonymous]

Caitlyn,

Thanks for the reply. I did in fact figure it out after I posted here. My complaint now is that there were not enough options on your form for me to properly categorize my question. I did the best I could, but for instance, I could not select Report Builder as the program. Kind of makes it hard when the problem is in Report Builder.

[Anonymous]

Alright, since I'm not getting any help on my previous post, I've decided to try it a different way. This is probably something very simple, but I have to ask since I am still new to Report Builder and what I'm trying right now isn't working.

I have a data cube that I am drawing car insurance data from. This data is sorted in my report by the city and state in which the claimant resides. (Both fields are listed in the cube under the Claimant category.) I have created a nested row group where City is under State (City is shown as Details2 in the Designer view).

I have also, since my last post, added in a new row that should be a percentage of the Direct Claim Deductible Recoverable End of Year total for that State or City. (The deductible total is listed at the bottom of that row.)

And that's why I come to you today. You see, the formula I tried to use for this was:

=First(Fields!Direct_Claim_Deductible_Recoverable_End_Of_Year.Value) / SUM(Fields!Direct_Claim_Deductible_Recoverable_End_Of_Year.Value)

And that gave me not a number responses for most rows, while any rows with a number (and bar) in them said 1.

I am sorting the State field in the table by [City], and the City field by [State].

Here's some sample output from this report:

What I need to know here is:

What formula do I need to use so that the Percentage column in the State rows actually calculates a percentage of the total Deductible?

And follow up questions:

1. How do I change the report view so that each City row in a particular state shows up under that State?

2. How do I adapt the formula above so that the City rows show a percentage of the State they are in?

[v-xiaoyan-msft]

Hi @Anonymous ,

Unfortunately I am unable to reproduce your problem, so in order to better and faster solve your problem in a targeted manner, we suggest  that if you are a Power BI Pro licensee, you can create a support ticket for free and a dedicated Microsoft engineer will come to solve the problem for you.
It would be great if you continue to share in this issue to help others with similar problems after you know the root cause or solution.

The link of Power BI Support: Support | Microsoft Power BI

For how to create a support ticket, please refer to How to create a support ticket in Power BI - Microsoft Power BI Community

Best Regards,
Community Support Team _ Caitlyn

[Anonymous]

Caitlyn,

I tried to submit a ticket, but I am not seeing the pages shown on the website. I was instead redirected to the Ideas submission page. How am I supposed to submit a ticket?