Help with modeling my information/ "Do Not Summarize" missing problem

[cole_lehmkuhler]

Okay so I have a few calculated percentages based 5 year warrenties that a company has to hand out in relation to total sales the company makes. I have the original purchase date of these items as the axis, but I also have a table of the Days in Service before these warrenties were claimed category as long as the date these claims were made. I'm moving this over from an excel file, but can't get my numbers to aggregate correctly. I'm trying to use the days in service category as my legend because I'm told to get the breakdown of whether the claim came after 0-90 days, 91-365 days, year 2, year 3, year 4, or year 5 after having the item. I have the purchase date in the axis again, the Days in service category in the legend field, and the percentages of warranties per total sales category in the values field. I also have the table below from excel which I figured would help to break up each section, but it hasn't helped. I think the whole problem has to do with an aggregate though, because I can't pick "do not summarize" so I have to either count my percentages or use the minimum or maximum of the percentages which obviously throws the numbers off. If this is confusing and you would like more information I should be able to respond quickly with a response to this and maybe a few pictures. Thanks!

START STOP DIS Label Days in Service

0	90	0 -90 DAYS	0-90
91	365	91-365 DAYS	91-365
366	730	YEAR 2	366-730
731	1095	YEAR 3	731-1095
1096	1460	YEAR 4	1096-1460
1461	1825	YEAR 5	1461-1825
1826	2190	YEAR 6	1826-2190
2191	2555	YEAR 7	2191-2555
2556	2920	YEAR 8	2556-2920
2921		No Purch Date	2921

[Nhallquist]

You are using a Date on the axis and then the Interaval range as the legend.  You have to summarize the legend.  Let's take a look at what you are trying to do.  What question do you want the chart to answer? Do you have an example of this chart, like a picture or drawing?  

Let us know,

Nate

[cole_lehmkuhler]

Thanks Nate, I want the chart to answer the total percentages of warrenties paid to total sales, but I need to be able to break it down by year, quarter, and month for the years 2013, 2014, 2015, and 2016. I also need the chart to be broken down to where I can see which warrenties were 0-90 days, 91-365 days, etc.

Here is my chart as of now, notice how all values are the same because I'm forced to take the max of my column "percentages of warrenties per adjusted sales (I meant adjusted sales in previous messages my mistake)

[Nhallquist]

OK.  We can work with Drill downs later, that will get us from the year level to the Quarter pretty quick.  I don't understand your calculation "percentages of warrenties per adjusted sales".  can you elaborate on that?  Are you looking at warranty $$'s against Adjusted Sales $$'s?

[cole_lehmkuhler]

Yes exactly that. I created the column just by dividing the amount of money due to warenties that were paid out by the amount of adjusted sales. They are both currency fields

[Nhallquist · Accepted solution]

Thanks.  I think that if you have this as a Calculation in the data, on each row, PBI needs to cummarize it some how.  Try breakin down that calculation and creating it in the object, so you are getting a % for the data in PBI.  does that make sense?  The formula might look something like:  (Sum(Warranty$)/Sum(Adjusted$)).

That will give you a total percentage that you can then break up by Interval category and plot that over time.

See if that works for you and let me know,

Nate

[cole_lehmkuhler]

That worked thanks a lot for your help!