SOLVED

Need help with PIVOT

%3CLINGO-SUB%20id%3D%22lingo-sub-1488623%22%20slang%3D%22de-DE%22%3ENeed%20help%20with%20PIVOT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1488623%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Unbenannt.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F200982i620464637E9F499A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Unbenannt.PNG%22%20alt%3D%22Unnamed.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%3EI%20want%20to%20make%20a%20single%20measured%20value%20line%20diagram%2C%20but%20the%20problem%20is%20that%20with%20the%20PIVOT%20the%20measured%20values%20add%20up%20and%20if%20I%20display%20the%20measured%20values%20every%20second%2C%20the%20diagram%20is%20very%20confusing.%3C%2FSPAN%3E%20%3CSPAN%3EIs%20there%20a%20way%20to%20hide%20the%20seconds%2C%20minutes%2C%20etc.%2C%20but%20the%20diagram%20does%20not%20change.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CSPAN%3ESorry%20for%20the%20English%20Use%20Google%20Translate%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%3Ethanks%20in%20advance%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1488623%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1489313%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20PIVOT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1489313%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F709965%22%20target%3D%22_blank%22%3E%40Felix285%3C%2FA%3E%26nbsp%3BYou%20can%20use%20a%20index%20column%20for%20it%20if%20you%20can%20provide%20a%20excel%20file%20for%20an%20example%20then%20I%20can%20explain%20you%20far%20better%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1491254%22%20slang%3D%22de-DE%22%3ERe%3A%20Need%20help%20with%20PIVOT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1491254%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F699938%22%20target%3D%22_blank%22%3E%40DevendraJain%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%3EIf%20you%20could%20help%20me%20with%20that%20it%20would%20be%20really%20nice%20of%20you.%3C%2FSPAN%3E%20%3CSPAN%3EI%20have%20now%20done%20an%20example%20in%20the%20appendix.%3C%2FSPAN%3E%3CBR%20%2F%3E%20%3CSPAN%3ETo%20understand%20again%2C%20I%20want%20all%20the%20data%20in%20the%20diagram%20to%20appear%2C%20but%20I%20want%20to%20keep%20the%20diagram%20clean%20and%20at%20most%20see%20the%20day%20(display%20all%20values%20individually)%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EThanks%20again%20many%20times%2C%20I%20have%20already%20wasted%20several%20hours%20on%20this!%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1492236%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20PIVOT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1492236%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F709965%22%20target%3D%22_blank%22%3E%40Felix285%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELinear%20chart%20doesn't%20work%20with%20hours%20and%20minutes%2C%20day%20is%20the%20minimum%20unit.%20What%20is%20on%20X-axis%20of%20your%20chart%20that's%20text%20which%20can't%20be%20transformed%2C%20it%20is%20as%20it%20is.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20such%20cases%20Scatter%20chart%20is%20used%2C%20but%20PivotTable%20doesn't%20work%20with%20such%20charts.%20The%20workaround%20is%20to%20generate%20data%20by%20other%20way%20to%20use%20Scatter%20chart.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20your%20sample%20I%20added%20column%20with%20averages%20and%20sorted%20source%20range%20in%20ascending%20order.%20However%2C%20better%20solution%20will%20be%20to%20Power%20Query%20the%20source%2C%20aggregate%20within%20it%20and%20return%20ready%20to%20chart%20table.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20check%20in%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1492376%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20PIVOT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1492376%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F709965%22%20target%3D%22_blank%22%3E%40Felix285%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22DevendraJain_0-1593185531426.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F201325iF0285161A0078BAA%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22DevendraJain_0-1593185531426.png%22%20alt%3D%22DevendraJain_0-1593185531426.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EPlease%20check%20this%20image%20I%20think%20this%20was%20what%20you%20needed.%20Also%20if%20you%20want%20the%20file%20do%20mail%20me%20I%20will%20give%20you%20the%20solution%20file%20on%20mail%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1492394%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20PIVOT%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1492394%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F699938%22%20target%3D%22_blank%22%3E%40DevendraJain%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFile%20is%20attached%20to%20previous%20posts%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Unbenannt.PNG

I want to make a single measured value line diagram, but the problem is that with the PIVOT the measured values add up and if I display the measured values every second, the diagram is very confusing. Is there a way to hide the seconds, minutes, etc., but the diagram does not change.

Sorry for the English Use Google Translate
 
thanks in advance
5 Replies

@Felix285 You can use a index column for it if you can provide a excel file for an example then I can explain you far better

@DevendraJain 

If you could help me with that it would be really nice of you. I have now done an example in the appendix.
To understand again, I want all the data in the diagram to appear, but I want to keep the diagram clean and at most see the day (display all values individually)
Thanks again many times, I have already wasted several hours on this!
best response confirmed by Felix285 (Occasional Contributor)
Solution

@Felix285 

Linear chart doesn't work with hours and minutes, day is the minimum unit. What is on X-axis of your chart that's text which can't be transformed, it is as it is.

 

In such cases Scatter chart is used, but PivotTable doesn't work with such charts. The workaround is to generate data by other way to use Scatter chart.

 

In your sample I added column with averages and sorted source range in ascending order. However, better solution will be to Power Query the source, aggregate within it and return ready to chart table.

 

Please check in attached file.

@Felix285 

DevendraJain_0-1593185531426.png

Please check this image I think this was what you needed. Also if you want the file do mail me I will give you the solution file on mail

 

@DevendraJain 

File is attached to previous posts