Multiple Stacked Small Visuals based on years and region

Frequent Visitor

3 years ago

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

Super User

3 years ago

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

Frequent Visitor

3 years ago

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

youngja_66
Frequent Visitor
3 years ago
Thank you for your response!

Forum Discussion

Multiple Stacked Small Visuals based on years and region

Related content

Stacked Column Chart with multiple values?

How to Expand Stacked Bars To fill Visual Pt2

How to show multiple metrics/measures in a visual?

Stacked Waterfall Chart with native visuals

Stack Multiple Columns