Multiple Stacked Small Visuals based on years and region

[youngja_66]

Hello PowerBI Users,

I am aiming to replicated in powerbi small visuals below.

Each visual is based on a region, each column is a year, and for each year 3 meaures are stacked

[Ritaf1983 · Accepted solution]

Hi @youngja_66 
We have this option with a stacked column chart << multiples :

In your scenario you need to put :
The region as "small multiples"
Year on X-axis
and measures on Y-axis
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[youngja_66]

Thank you for your response!

[youngja_66]

The measure for some of the years eg. 2007, 2013, 2017, 2020 are within the fact table, but year measures in between are calculated e.g. 2008 = 2007 + (2013-2007) * 1/6 . 

2007,2013,2017,2020 measure is dervied directly from the fact table

e.g. 2008

If i put the x axis as 'year' very odd output

I'm thinking I am going to have to use DAX to create a pivot table similar to this where I can calc the meaures for the in between years?

[Ritaf1983]

Hi @youngja_66 

Unfortunately, I can't understand the problems of the model from the pictures.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[youngja_66]

Example Fact Table Structure:

Value	Unique_ID	Orig_Ct	NSW_ID	LLS_ID	LGA_ID	IBRA_ID	SVTM_vForm_ID	Ind_Ct	Area	Min	Max	Range	Mean	Std	Sum	Year	Indicator
2290	EC_2007_2290	51705	1	10	103	8	15	51696	418737600	0	0.944843054	0.944843054	0.687336094	0.127962689	35532.52673	2007	Ecological Condition
2295	EC_2007_2295	4	1	10	103	8	9	4	32400	0.411207795	0.636495113	0.225287318	0.545719996	0.08301727	2.182879984	2007	Ecological Condition
2290	EC_2013_2290	51705	1	10	103	8	15	51657	418421700	0.21449241	0.946917593	0.732425183	0.740838624	0.087592803	38269.50081	2013	Ecological Condition
2295	EC_2013_2295	4	1	10	103	8	9	4	32400	0.6188851	0.813755989	0.194870889	0.727531433	0.076697191	2.910125732	2013	Ecological Condition
2290	EC_2017_2290	51705	1	10	103	8	15	51690	418689000	0.015094439	0.948723376	0.933628936	0.762080524	0.071695783	39391.9423	2017	Ecological Condition
2295	EC_2017_2295	4	1	10	103	8	9	4	32400	0.732422769	0.835702419	0.10327965	0.789653838	0.037326436	3.158615351	2017	Ecological Condition
2290	EC_2020_2290	51705	1	10	103	8	15	51690	418689000	0.001159304	0.948723376	0.947564072	0.412688293	0.277582108	21331.85789	2020	Ecological Condition
2295	EC_2020_2295	4	1	10	103	8	9	4	32400	0.356333315	0.787673414	0.431340098	0.486190997	0.175087057	1.944763988	2020	Ecological Condition
2290	ECC_2007_2290	51705	1	10	103	8	15	51696	418737600	0.324601471	0.758608401	0.434006929	0.55558939	0.084664126	28721.7491	2007	Ecological Carrying Capacity
2295	ECC_2007_2295	4	1	10	103	8	9	4	32400	0.450964749	0.525487542	0.074522793	0.49036146	0.029460963	1.961445838	2007	Ecological Carrying Capacity
2290	ECC_2007_2290	51705	1	10	103	8	15	51657	418421700	0.381683677	0.773083746	0.391400069	0.620271764	0.065237538	32041.3785	2013	Ecological Carrying Capacity
2295	ECC_2007_2295	4	1	10	103	8	9	4	32400	0.567748368	0.687787235	0.120038867	0.649554774	0.048170151	2.598219097	2013	Ecological Carrying Capacity
2290	ECC_2017_2290	51705	1	10	103	8	15	51690	418689000	0.4077034	0.776736081	0.369032681	0.643455697	0.056506326	33260.22498	2017	Ecological Carrying Capacity
2295	ECC_2017_2295	4	1	10	103	8	9	4	32400	0.641881466	0.716000795	0.074119329	0.689931825	0.028902589	2.759727299	2017	Ecological Carrying Capacity
2290	ECC_2020_2290	51705	1	10	103	8	15	51690	418689000	0.074755304	0.751456201	0.676700898	0.355372447	0.190074305	18369.20181	2020	Ecological Carrying Capacity
2295	ECC_2020_2295	4	1	10	103	8	9	4	32400	0.232444331	0.639404893	0.406960562	0.338427275	0.173808419	1.353709102	2020	Ecological Carrying Capacity
115	EC_2007_115	2078	1	5	77	9	12	2078	16831800	0.029066242	0.699744403	0.670678161	0.44236294	0.08950457	919.2301893	2007	Ecological Condition
116	EC_2007_116	673439	1	5	77	9	1	673389	5454450900	0.0190027	0.82767725	0.80867455	0.136503701	0.150043629	91920.09097	2007	Ecological Condition
115	EC_2013_115	2078	1	5	77	9	12	2078	16831800	0.031203886	0.592478871	0.561274985	0.428333057	0.074106383	890.0760921	2013	Ecological Condition
116	EC_2013_116	673439	1	5	77	9	1	673389	5454450900	0.009607106	0.844982028	0.835374922	0.130955547	0.14048723	88184.02502	2013	Ecological Condition
115	EC_2017_115	2078	1	5	77	9	12	2078	16831800	0.052943852	0.619355738	0.566411886	0.405781585	0.068066642	843.2141332	2017	Ecological Condition
116	EC_2017_116	673439	1	5	77	9	1	673389	5454450900	0.019842796	0.831962824	0.812120028	0.142165591	0.128297757	95732.74517	2017	Ecological Condition
115	EC_2020_115	2078	1	5	77	9	12	2078	16831800	0.052943852	0.619355738	0.566411886	0.405781585	0.068066642	843.2141332	2020	Ecological Condition
116	EC_2020_116	673439	1	5	77	9	1	673389	5454450900	0.019842796	0.831962824	0.812120028	0.142165591	0.128297757	95732.74517	2020	Ecological Condition
115	ECC_2007_115	2078	1	5	77	9	12	2078	16831800	0.085629694	0.324878395	0.2392487	0.231679868	0.031909827	481.4307658	2007	Ecological Carrying Capacity
116	ECC_2007_116	673439	1	5	77	9	1	673389	5454450900	0.03279813	0.559352219	0.526554089	0.111690946	0.058144725	75211.45448	2007	Ecological Carrying Capacity
115	ECC_2007_115	2078	1	5	77	9	12	2078	16831800	0.08314655	0.263643265	0.180496715	0.224547295	0.024705304	466.6092789	2013	Ecological Carrying Capacity
116	ECC_2007_116	673439	1	5	77	9	1	673389	5454450900	0.03077851	0.579967737	0.549189227	0.106883898	0.05925736	71974.44125	2013	Ecological Carrying Capacity
115	ECC_2017_115	2078	1	5	77	9	12	2078	16831800	0.082945466	0.253764421	0.170818955	0.212137274	0.021501182	440.8212553	2017	Ecological Carrying Capacity
116	ECC_2017_116	673439	1	5	77	9	1	673389	5454450900	0.039400052	0.578844965	0.539444912	0.106433479	0.054607638	71671.13409	2017	Ecological Carrying Capacity
115	ECC_2020_115	2078	1	5	77	9	12	2078	16831800	0.082945466	0.253764421	0.170818955	0.212137274	0.021501182	440.8212549	2020	Ecological Carrying Capacity
116	ECC_2020_116	673439	1	5	77	9	1	673389	5454450900	0.039400052	0.578792155	0.539392103	0.106292542	0.054483909	71576.22868	2020	Ecological Carrying Capacity

If this was done in excel and example is piovt of the above table

Columns 2008 - 2012 are simply a calculation between 2007 & 2013 i.e. interpolated

Columns 2007, 2013, 2017, 2020 are in the fact table 

IBRA_ID	Indicator	2007	2013	2017	2020	2008	2009	2010	2011	2012
8	Ecological Carrying Capacity	0.555584	0.620274	0.643459	0.355371	0.566366	0.577148	0.587929	0.598711	0.609492
8	Ecological Condition	0.687325	0.740838	0.762083	0.412694	0.696244	0.705163	0.714081	0.723	0.731919
9	Ecological Carrying Capacity	0.11206	0.107246	0.106759	0.106618	0.111258	0.110455	0.109653	0.108851	0.108048
9	Ecological Condition	0.137445	0.13187	0.142977	0.142977	0.136516	0.135587	0.134658	0.133728	0.132799

Expected Small Visuals are based on IBRA_ID, Year.  Stacked graph with each stack representing a single year of the indicator column

At the moment in powerbi, I create measurses for 2007, 2013, 2017, 2020 for each indcator

e.g. 

EC 2007 =

VAR _SumOfCount = CALCULATE(SUM('tbl_FACT'[Ind_Ct]), 'tbl_FACT'[Year] IN { 2007 }, 'tbl_FACT'[Indicator] IN { "Ecological Condition"})

VAR _SumOfSum = CALCULATE(SUM('tbl_FACT'[Sum]), 'tbl_FACT'[Year] IN { 2007 }, 'tbl_FACT'[Indicator] IN { "Ecological Condition"})

VAR Result =

    DIVIDE( _SumOfSum, _SumOfCount )

RETURN

    Result

For the interpolated years 2008 - 2012 i create a measure e.g.

EC 2008 = CALCULATE([EC 2007] + ([EC 2013]-[EC 2007]) * (1/6))

Hope this makes more sense