Forum Discussion
Unable to make consolidate data for MIS through Pivot looking other option in excel
Hello connection
I am looking for one help in consolidation of data in excel however desired result are not coming through pivot looking alternate unable to make,
File is enclosed user can add columns if required for result or source data
Appreciate for help
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%
5 Replies
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.
