Forum Discussion
Unable to make consolidate data for MIS through Pivot looking other option in excel
Thanks for responding SergeiBaklan
Really appreciate on work done & providing suggestion how to make
can you please share working file on my email: mukhi_amit@yahoo.com i will do some working on same file i am not very advance user in excel
For reporting purpose out of 11 ID if one is discrepant than my FTR calculation will be
FTR% is = FTR/count of ID
Discrepant% = Discrepant/Count of ID
because in source data for location 1 we have two results for one ID refer S No 15 & 16, rule applied for reporting purpose is where ever there is discrepancy that ID will overall assume discrepant irrespective that ID is having one FTR row as well
In other way if you have 10 set of pen assuming 1 set = 2 pen and out of this ( 10 set= 20 pen) if 1 pen is broken than i will assume your 1 set is broken so rest of the pen set you have is 9 in other words 90% (Pen set ok) 10% (broken) here in table it should be 11-1-10 my % will come out automatically
Branch Count of ID Discrepant FTR FTR % Discrepant % Location1 11 1 11 68.8% 6.3% Location2 5 5 0 0.0% 31.3%
You may add data to data model creating the Pivot Table and after that in Power Pivot or using right click menu in Pivot Table create 5 measures:
as
Count of ID:=DISTINCTCOUNT(Table1[ID])
Discrepant:=CALCULATE(COUNTROWS(VALUES(Table1[ID])),Table1[Overall Status]="Discrepant")
FTR:=CALCULATE(COUNTROWS(VALUES(Table1[ID])),Table1[Overall Status]="FTR")
Discrepant %:=[Discrepant]/CALCULATE(COUNTROWS(VALUES(Table1[ID])),ALL(Table1[Location]))
FTR %:=[FTR]/CALCULATE(COUNTROWS(VALUES(Table1[ID])),ALL(Table1[Location]))Not sure how % shall be calculated, here is to distinct ID:s for all location. If not, when ALL shall be removed.
Result is
Result is bit different from your manual sample, but again, not sure in logic.
Thanks for responding SergeiBaklan
Really appreciate on work done & providing suggestion how to make
can you please share working file on my email: mukhi_amit@yahoo.com i will do some working on same file i am not very advance user in excel
For reporting purpose out of 11 ID if one is discrepant than my FTR calculation will be
FTR% is = FTR/count of ID
Discrepant% = Discrepant/Count of ID
because in source data for location 1 we have two results for one ID refer S No 15 & 16, rule applied for reporting purpose is where ever there is discrepancy that ID will overall assume discrepant irrespective that ID is having one FTR row as well
In other way if you have 10 set of pen assuming 1 set = 2 pen and out of this ( 10 set= 20 pen) if 1 pen is broken than i will assume your 1 set is broken so rest of the pen set you have is 9 in other words 90% (Pen set ok) 10% (broken) here in table it should be 11-1-10 my % will come out automatically
| Branch | Count of ID | Discrepant | FTR | FTR % | Discrepant % |
| Location1 | 11 | 1 | 11 | 68.8% | 6.3% |
| Location2 | 5 | 5 | 0 | 0.0% | 31.3% |
I added one more measure to exclude from counting ID:s which are in both categories.
Exclude ID:=COUNTROWS( INTERSECT( CALCULATETABLE(DISTINCT(Table1[ID]),Table1[Overall Status]="Discrepant"), CALCULATETABLE(DISTINCT(Table1[ID]),Table1[Overall Status]="FTR") ) )and updated the rest accordingly. Sorry, but I still don't understand initial manual table. Why in Location 1 9 FTR, not 10 (we have totally 11 with unique ID minus 1 together with discrepant = 10), why discrepant % for location 2 is 50%, not 100%.
Anyway, the latest file is attached to this post.
Response awaited sir ...
Kindly help for closure
Greetings for the day sirSergeiBaklan
you are correct sir i think by mistake i uploaded my old working file due to confusion created
Apology for providing wrong sheet , have gone through my source data again table is updated below please ignore table in attachment, i hope this will clear further doubts
Request relook please
For location 1 count of ID is appearing 10 in attachment shared count will 11 & % will change accordingly
For location 2 result ok
One again apology for wrong calculation provided in attachment
Correct output table is as under
Branch Count of ID Discrepant FTR FTR % Discrepant% Location1 11 1 10 91% 9% Location2 5 5 0 0% 100% Okay, so Count of ID are all ID:s includes ones with FTR and Discrepant at the same time. I updated the measure, it looks like we are sync now
File is attached.