Help with a pivot

%3CLINGO-SUB%20id%3D%22lingo-sub-3492850%22%20slang%3D%22en-US%22%3EHelp%20with%20a%20pivot%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3492850%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20that%20has%20columns%20Deal%20Name%2C%20Owner%2C%20Phase%201%2C%20Phase%202%2C%20and%20Phase%203.%26nbsp%3B%20The%20Phase%20columns%20will%20have%20the%20date%20the%20deal%20has%20completed%20that%20phase%3B%20so%20it%20can%20be%20blank%20if%20not%20completed%20yet.%26nbsp%3B%20I%20want%20to%20count%20the%20number%20of%20Phase%201%2C%20Phase%202%20and%20Phase%203%20deals%20completed%20(only%20requirement%20is%20there%20is%20a%20date%20in%20this%20field)%20by%20Owner.%26nbsp%3B%20Confused%20on%20how%20to%20pivot%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3492850%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3494837%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20pivot%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3494837%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1420303%22%20target%3D%22_blank%22%3E%40aaashton%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20seems%20to%20work%3A%3C%2FP%3E%3CP%3EFirst%20the%20raw%20data%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1654891046619.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22mathetes_0-1654891046619.png%22%20style%3D%22width%3A%20360px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22mathetes_0-1654891046619.png%22%20style%3D%22width%3A%20360px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F379442i23CD03245DEBA6EC%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mathetes_0-1654891046619.png%22%20alt%3D%22mathetes_0-1654891046619.png%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThen%20the%20pivot%20table%20instructions%20(dialog%20box)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_1-1654891102519.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22mathetes_1-1654891102519.png%22%20style%3D%22width%3A%20286px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22mathetes_1-1654891102519.png%22%20style%3D%22width%3A%20286px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F379443i6B4A590EA07A327F%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mathetes_1-1654891102519.png%22%20alt%3D%22mathetes_1-1654891102519.png%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAnd%20the%20resulting%20pivot%20table%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_2-1654891171631.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22mathetes_2-1654891171631.png%22%20style%3D%22width%3A%20362px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22mathetes_2-1654891171631.png%22%20style%3D%22width%3A%20362px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F379445iC006272233946EB1%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mathetes_2-1654891171631.png%22%20alt%3D%22mathetes_2-1654891171631.png%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ESpreadsheet%20attached%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3495844%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20pivot%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3495844%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1420303%22%20target%3D%22_blank%22%3E%40aaashton%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20addition%20to%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3Band%20assuming%20you%20are%20not%20on%20Mac%2C%20to%20calculate%20all%20phases%20completed%20you%20may%20add%20data%20to%20data%20model%20creating%20PivotTable%20and%20use%20such%20measure%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3ECompleted%20%3A%3D%0ACALCULATE%20(%0A%20%20%20%20COUNTROWS%20(%20Table1%20)%2C%0A%20%20%20%20Table1%5BPhase1%5D%20%26gt%3B%201%2C%0A%20%20%20%20Table1%5BPhase2%5D%20%26gt%3B%201%2C%0A%20%20%20%20Table1%5BPhase3%5D%20%26gt%3B%201%0A)%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20847px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20847px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F379519i8A63E9F2B1E0C5E6%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello,

I have a spreadsheet that has columns Deal Name, Owner, Phase 1, Phase 2, and Phase 3.  The Phase columns will have the date the deal has completed that phase; so it can be blank if not completed yet.  I want to count the number of Phase 1, Phase 2 and Phase 3 deals completed (only requirement is there is a date in this field) by Owner.  Confused on how to pivot this?

2 Replies

@aaashton 

 

This seems to work:

First the raw data

mathetes_0-1654891046619.png

Then the pivot table instructions (dialog box)

mathetes_1-1654891102519.png

And the resulting pivot table

mathetes_2-1654891171631.png

Spreadsheet attached

 

@aaashton 

In addition to @mathetes and assuming you are not on Mac, to calculate all phases completed you may add data to data model creating PivotTable and use such measure

Completed :=
CALCULATE (
    COUNTROWS ( Table1 ),
    Table1[Phase1] > 1,
    Table1[Phase2] > 1,
    Table1[Phase3] > 1
)

 

image.png