Forum Discussion

linkamitmukhi's avatar
linkamitmukhi
Copper Contributor
Jan 16, 2020
Solved

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 co...
Need Help
  • linkamitmukhi's avatar
    linkamitmukhi
    Jan 22, 2020

    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 

     

    BranchCount of IDDiscrepantFTRFTR %Discrepant %
    Location111111 68.8%6.3%
    Location25500.0%31.3%

     

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 21, 2020

linkamitmukhi 

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.

  • linkamitmukhi's avatar
    linkamitmukhi
    Copper Contributor
    Jan 22, 2020

    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 

     

    BranchCount of IDDiscrepantFTRFTR %Discrepant %
    Location111111 68.8%6.3%
    Location25500.0%31.3%

     

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jan 23, 2020

      linkamitmukhi 

      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.

      • linkamitmukhi's avatar
        linkamitmukhi
        Copper Contributor
        Jan 23, 2020

         

        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 

        BranchCount of IDDiscrepantFTRFTR %Discrepant%
        Location11111091%9%
        Location25500%100%

         

Resources