Forum Discussion
Creating Chart from multiple Pivot tables
I didn't sort months/periods in previous variant, corrected in attached.
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
It like this
but very depends on which kind of chart you'd like to have.
SergeiBaklan 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
I didn't sort months/periods in previous variant, corrected in attached.
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
Please check in attached