SOLVED

PivotTable

%3CLINGO-SUB%20id%3D%22lingo-sub-3294757%22%20slang%3D%22en-US%22%3EPivotTable%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3294757%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20more%20than%205%20sheets%20having%20the%20same%20fields%20i.e.%20Date%20and%20Symbol%2C%20the%20only%20difference%20among%20is%20the%20point%20of%20occurrence%20i.e.%20date%2C%20Pivot%20Table%20is%20not%20allowing%20me%20to%20create%20relations%20among%20due%20to%20same%20fields%2C%20please%20help%20out.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3294757%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3295436%22%20slang%3D%22en-US%22%3ERe%3A%20PivotTable%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3295436%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1371991%22%20target%3D%22_blank%22%3E%40ykhetrapal%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20use%20the%20same%20slicer%20for%20few%20PivotTables%20if%20they%20are%20based%20on%20same%20data%20source.%20The%20rest%20is%20not%20clear%20for%20me.%20Perhaps%20with%20data%20model%20and%20DAX%20measures%20you%20may%20build%20cross-filtering.%20But%20in%20any%20case%20you%20need%20to%20have%20something%20in%20Values%2C%20otherwise%20it%20doesn't%20work.%20With%20nothing%20to%20aggregate%20you%20have%20all%20values%20in%20rows.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3295352%22%20slang%3D%22en-US%22%3ERe%3A%20PivotTable%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3295352%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20really%20thankful%20to%20you%2C%20this%20is%20the%20first%20Pivot%20I've%20made%20just%20coz%20of%20you.%26nbsp%3B%20With%20curiosity%20to%20make%20it%20much%20smarter%20have%20come%20up%20with%20below%20points%3B%3C%2FP%3E%3CUL%3E%3CLI%3Eif%20we%20can%20control%20multiple%20pivots%20through%20one%20slicer%20only%2C%20for%20the%20months%20mentioned%20above%3F%3C%2FLI%3E%3CLI%3Eif%20there%20is%20option%20to%20cross%20verify%20the%20availability%20of%20Symbol%20in%20multiple%20Pivot%3F%3C%2FLI%3E%3CLI%3EHow%20can%20I%20have%20the%20dates%20below%20the%20months%3F%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ykhetrapal_3-1650989407188.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F366960iEFE674A617EDA27B%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22ykhetrapal_3-1650989407188.png%22%20alt%3D%22ykhetrapal_3-1650989407188.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ykhetrapal_4-1650989596210.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F366962iC3DC4E19146B8337%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22ykhetrapal_4-1650989596210.png%22%20alt%3D%22ykhetrapal_4-1650989596210.png%22%20%2F%3E%3C%2FSPAN%3E%3CBR%20%2F%3E%3CP%3E%3CSPAN%3EPlease%20also%20advice%2C%20if%20can%20be%20improved%20in%20other%20ways.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLI%3E%3C%2FUL%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3294810%22%20slang%3D%22en-US%22%3ERe%3A%20PivotTable%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3294810%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1371991%22%20target%3D%22_blank%22%3E%40ykhetrapal%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDepends%20on%20goals.%20If%20you'd%20like%20to%20consolidate%20all%20data%20in%20one%20PivotTable%20you%20may%20use%20PivotTable%20and%20PivotChart%20Wizard%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.contextures.com%2Fxlpivot08.html%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EExcel%20Pivot%20Table%20Multiple%20Consolidation%20Ranges%20(contextures.com)%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Deleted
Not applicable

Hi all,

 

I have more than 5 sheets having the same fields i.e. Date and Symbol, the only difference among is the point of occurrence i.e. date, Pivot Table is not allowing me to create relations among due to same fields, please help out.

3 Replies
best response
Solution

@Deleted 

Depends on goals. If you'd like to consolidate all data in one PivotTable you may use PivotTable and PivotChart Wizard Excel Pivot Table Multiple Consolidation Ranges (contextures.com)

@Sergei Baklan 

I'm really thankful to you, this is the first Pivot I've made just coz of you.  With curiosity to make it much smarter have come up with below points;

  • if we can control multiple pivots through one slicer only, for the months mentioned above?
  • if there is option to cross verify the availability of Symbol in multiple Pivot?
  • How can I have the dates below the months?ykhetrapal_3-1650989407188.pngykhetrapal_4-1650989596210.png

    Please also advice, if can be improved in other ways.

@Deleted 

You may use the same slicer for few PivotTables if they are based on same data source. The rest is not clear for me. Perhaps with data model and DAX measures you may build cross-filtering. But in any case you need to have something in Values, otherwise it doesn't work. With nothing to aggregate you have all values in rows.