SOLVED
Home

Creating Chart from multiple Pivot tables

%3CLINGO-SUB%20id%3D%22lingo-sub-862716%22%20slang%3D%22en-US%22%3ECreating%20Chart%20from%20multiple%20Pivot%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-862716%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20set%20of%20data%20as%20uploaded.%3C%2FP%3E%3CP%3E%26nbsp%3BI%20need%20to%20have%20distinct%20count%20from%20suppliers%20within%20a%20specific%20period.%3C%2FP%3E%3CP%3EThe%20details%20are%20mentioned%20in%20the%20excel%20data%20itself.%20Can%20someone%20please%20help%20me%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-862716%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-863537%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20Chart%20from%20multiple%20Pivot%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-863537%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F134141%22%20target%3D%22_blank%22%3E%40Ubaid%20ur%20Rahman%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20better%20to%20do%20with%20DAX.%20First%2C%20create%20Date%20table%20in%20your%20model%20(to%20simplify%20could%20be%20done%20within%20Power%20Pivot%20clicking%20on%20New%20Date%20table)%20and%20link%20it%20one-to-many%20with%20your%20Range%20table.%3C%2FP%3E%0A%3CP%3ENext%20add%20two%20measures%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3EVendor%20Distinct%20Count%20%3A%3D%0ADISTINCTCOUNT%20(%20Range%5BVendor%5D%20)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ERunning%20Year%20Distinct%20Count%3A%3DCALCULATE%20(%0A%20%20%20%20%5BVendor%20Distinct%20Count%5D%2C%0A%20%20%20%20DATESINPERIOD%20(%20'Calendar'%5BDate%5D%2C%20MAX%20(%20'Calendar'%5BDate%5D%20)%2C%20-1%2C%20YEAR%20)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20build%20Pivot%20Table%20from%20data%20model%20using%20measures%2C%20connect%20existing%20slicers%20to%20that%20Pivot%20Table.%3C%2FP%3E%0A%3CP%3EResult%20is%20as%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20526px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F132626iB6D0CF04FCC90A77%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIt%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20474px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F132628i3E9E95AF7E254AC3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ebut%20very%20depends%20on%20which%20kind%20of%20chart%20you'd%20like%20to%20have.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-866604%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20Chart%20from%20multiple%20Pivot%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-866604%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%3BDear%20Sergei%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20for%20your%20answer.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EActually%20I%20need%20to%20compare%20yearly%20increase%20or%20decrease%20of%20our%20suppliers%20in%20database.%26nbsp%3BLike%20for%20month%20Dec%2018%20I%20will%20take%20the%20values%20of%20Jan%202018-Dec%202018.%20(sorry%20by%20mistake%20I%20mentioned%20wrong%20date).%3C%2FP%3E%3CP%3EIs%20it%20possible%20to%20make%20such%20buckets%20of%20the%20following...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EDec%2019%3C%2FTD%3E%3CTD%3EJan%2018%20-%20Dec%2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJan%2019%3C%2FTD%3E%3CTD%3EFeb%2018%20-%20Jan%2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EFeb%2019%3C%2FTD%3E%3CTD%3EMrz%2018%20-%20Feb%2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EMrz%2019%3C%2FTD%3E%3CTD%3EApril%2018%20-%20Mrz%2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EApr%2019%3C%2FTD%3E%3CTD%3EMay%2018%20-%20April%2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EMai%2019%3C%2FTD%3E%3CTD%3EJune%2018%20-%20May%2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJun%2019%3C%2FTD%3E%3CTD%3EJuly%2018%20-%20June%2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EJul%2019%3C%2FTD%3E%3CTD%3EAug%2018%20-%20July%2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAug%2019%3C%2FTD%3E%3CTD%3ESep%2018%20-%20Aug%2019%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethen%20I%20want%20to%20simply%20show%20in%20bar%20chart%20where%20we%20can%20easily%20see%20the%20number%20decreased%20or%20increased.%3C%2FP%3E%3CP%3EIt%20would%20be%20great%20if%20I%20can%20do%20the%20same%20on%20quarterly%20basis.%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-866893%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20Chart%20from%20multiple%20Pivot%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-866893%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%3BHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20I%20got%20it.%20I%20just%20selected%20the%20months%20I%20wanted%20and%20it%20gives%20me%20th%20result.%3C%2FP%3E%3CP%3ENow%20how%20can%20i%20do%20this%20for%20quarterly%20basis%3F%3C%2FP%3E%3CP%3ESecondly%2C%20is%20there%20any%20way%20to%20show%20the%20Grand%20total%20in%20Bar%20chart%3F%20i%20am%20not%20able%20to%20do%20it.%3C%2FP%3E%3CP%3ESee%20the%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-868230%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20Chart%20from%20multiple%20Pivot%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-868230%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F134141%22%20target%3D%22_blank%22%3E%40Ubaid%20ur%20Rahman%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3EYou%20may%20add%20more%20calculated%20columns%20to%20Dates%20table%20in%20data%20model.%3C%2FP%3E%0A%3CP%3EFor%20Periods%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DFORMAT(DATEADD('Calendar'%5BDate%5D%2C-1%2CYEAR)%2C%22MMM%20YY%22)%20%26amp%3B%0A%20%20%22%20-%20%22%20%26amp%3B%20FORMAT(%5BDate%5D%2C%22MMM%20YY%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EFor%20Quarters%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DROUNDUP(MONTH('Calendar'%5BDate%5D)%2F3%2C0)%20%26amp%3B%20%22Q%22%20%26amp%3B%20FORMAT('Calendar'%5BDate%5D%2C%22YY%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EAdd%20Pivot%20Table%20and%20Pivot%20Chart%20using%20quarters%20and%20aggregating%20Vendor%20Distinct%20Count%20measure.%3C%2FP%3E%0A%3CP%3EAll%20together%20is%20as%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20970px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F132824i6E31A4315CE7015E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EPlease%20check%20in%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-868232%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20Chart%20from%20multiple%20Pivot%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-868232%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F134141%22%20target%3D%22_blank%22%3E%40Ubaid%20ur%20Rahman%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20didn't%20sort%20months%2Fperiods%20in%20previous%20variant%2C%20corrected%20in%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Ubaid ur Rahman
Contributor

I have a set of data as uploaded.

 I need to have distinct count from suppliers within a specific period.

The details are mentioned in the excel data itself. Can someone please help me?

 

Thanks in advance.

5 Replies

@Ubaid ur Rahman 

That's better to do with DAX. First, create Date table in your model (to simplify could be done within Power Pivot clicking on New Date table) and link it one-to-many with your Range table.

Next add two measures

Vendor Distinct Count :=
DISTINCTCOUNT ( Range[Vendor] )

and

Running Year Distinct Count:=CALCULATE (
    [Vendor Distinct Count],
    DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -1, YEAR )
)

and build Pivot Table from data model using measures, connect existing slicers to that Pivot Table.

Result is as

image.png

It like this

image.png

but very depends on which kind of chart you'd like to have.

@Sergei Baklan Dear Sergei,

 

Thank you very much for your answer.

 

Actually I need to compare yearly increase or decrease of our suppliers in database. Like for month Dec 18 I will take the values of Jan 2018-Dec 2018. (sorry by mistake I mentioned wrong date).

Is it possible to make such buckets of the following...

 

Dec 19Jan 18 - Dec 19
Jan 19Feb 18 - Jan 19
Feb 19Mrz 18 - Feb 19
Mrz 19April 18 - Mrz 19
Apr 19May 18 - April 19
Mai 19June 18 - May 19
Jun 19July 18 - June 19
Jul 19Aug 18 - July 19
Aug 19Sep 18 - Aug 19

 

then I want to simply show in bar chart where we can easily see the number decreased or increased.

It would be great if I can do the same on quarterly basis.

Thank you

 

@Sergei Baklan Hi,

 

I think I got it. I just selected the months I wanted and it gives me th result.

Now how can i do this for quarterly basis?

Secondly, is there any way to show the Grand total in Bar chart? i am not able to do it.

See the file.

 

Thank you

 

@Ubaid ur Rahman 

Hi,

You may add more calculated columns to Dates table in data model.

For Periods

=FORMAT(DATEADD('Calendar'[Date],-1,YEAR),"MMM YY") &
  " - " & FORMAT([Date],"MMM YY")

For Quarters

=ROUNDUP(MONTH('Calendar'[Date])/3,0) & "Q" & FORMAT('Calendar'[Date],"YY")

Add Pivot Table and Pivot Chart using quarters and aggregating Vendor Distinct Count measure.

All together is as

image.png

Please check in attached

Solution

@Ubaid ur Rahman 

I didn't sort months/periods in previous variant, corrected in attached.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
21 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies