Forum Discussion
Multiple Stacked Small Visuals based on years and region
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
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?
Super UserHi 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
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 )RETURNResultFor 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