Stacked bar chart with two different shared axis

Question

Hello,I have the following table F_KPI_ADDRESS with the fields:date : contains last date of monthsnumber: contains the number of records in that month, for a certain part and a certain type&nbsp;number_OK:&nbsp;contains the number of records in that month, for a certain part and a certain type that meet the KPIpart: one of following values: "street", "number", "zip code", "city"type: one of following values: "organization", "person"I added two calculated columns needed for the visuals:month = FORMAT(F_KPI_ADDRESS[date]; "mmmm yyyy"), field is ordered by&nbsp;month_ordermonth_order = FORMAT(F_KPI_ADDRESS[date]; "yyyymm")&nbsp;F_KPI_ADDRESSdatenumbernumber_OKparttypemonthmonth_order31/05/202010998streetpersonMay 202020200531/05/2020389numberpersonMay 202020200531/05/2020285264zip codepersonMay 202020200531/05/202020329citypersonMay 202020200531/05/20205127streetorganizationMay 202020200531/05/20206448numberorganizationMay 202020200531/05/20207047zip codeorganizationMay 202020200531/05/20203730cityorganizationMay 202020200530/06/20203326streetpersonJune 202020200630/06/20204541numberpersonJune 202020200630/06/202024360zip codepersonJune 202020200630/06/20203616citypersonJune 202020200630/06/2020264streetorganizationJune 202020200630/06/2020247176numberorganizationJune 202020200630/06/20209341zip codeorganizationJune 202020200630/06/2020266191cityorganizationJune 202020200631/07/20201264streetpersonJuly 202020200731/07/20208514numberpersonJuly 202020200731/07/2020212151zip codepersonJuly 202020200731/07/20202004citypersonJuly 202020200731/07/202013446streetorganizationJuly 202020200731/07/202019260numberorganizationJuly 202020200731/07/20202066zip codeorganizationJuly 202020200731/07/202018130cityorganizationJuly 202020200731/08/2020283194streetpersonAugust 202020200831/08/20209789numberpersonAugust 202020200831/08/202018953zip codepersonAugust 202020200831/08/2020231181citypersonAugust 202020200831/08/20202945streetorganizationAugust 202020200831/08/2020124109numberorganizationAugust 202020200831/08/202030775zip codeorganizationAugust 202020200831/08/202021027cityorganizationAugust 2020202008The following measures I made to represent in a&nbsp;Stacked bar chart:Percentage_KPI_ADDRESS =&nbsp;ROUND( DIVIDE( SUM(F_KPI_ADDRESS[number_OK]); SUM(F_KPI_ADDRESS[number]) ); 4 )Percentage_Street_Organization =&nbsp;CALCULATE( [Percentage_KPI_ADDRESS]; F_KPI_ADDRESS[part] = "street"; F_KPI_ADDRESS[type] = "organization" )Percentage_Street_Person =&nbsp;CALCULATE( [Percentage_KPI_ADDRESS]; F_KPI_ADDRESS[part] = "street"; F_KPI_ADDRESS[type] = "person" )Percentage_Number_Organization =&nbsp;CALCULATE( [Percentage_KPI_ADDRESS]; F_KPI_ADDRESS[part] = "number"; F_KPI_ADDRESS[type] = "organization" )Percentage_Number_Person =&nbsp;CALCULATE( [Percentage_KPI_ADDRESS]; F_KPI_ADDRESS[part] = "number"; F_KPI_ADDRESS[type] = "person" )Percentage_Zip_Code_Organization =&nbsp;CALCULATE( [Percentage_KPI_ADDRESS]; F_KPI_ADDRESS[part] = "zip code"; F_KPI_ADDRESS[type] = "organization" )Percentage_Zip_Code_Person =&nbsp;CALCULATE( [Percentage_KPI_ADDRESS]; F_KPI_ADDRESS[part] = "zip code"; F_KPI_ADDRESS[type] = "person" )Percentage_City_Organization =&nbsp;CALCULATE( [Percentage_KPI_ADDRESS]; F_KPI_ADDRESS[part] = "city"; F_KPI_ADDRESS[type] = "organization" )Percentage_City_Person =&nbsp;CALCULATE( [Percentage_KPI_ADDRESS]; F_KPI_ADDRESS[part] = "city"; F_KPI_ADDRESS[type] = "person")&nbsp;In the&nbsp;Stacked bar chart I want to see both&nbsp;F_KPI_ADDRESS[month] as&nbsp;F_KPI_ADDRESS[type]&nbsp;on the X axisThe Values for&nbsp;F_KPI_ADDRESS[type] = "organization":&nbsp;Percentage_Street_Organization,&nbsp;Percentage_Number_Organization,&nbsp;Percentage_Zip_Code_Organization and&nbsp;Percentage_City_Organization&nbsp;respectively as street, number, zip code and cityThe stacked values for&nbsp;F_KPI_ADDRESS[type] = "person":&nbsp;Percentage_Street_Person,&nbsp;Percentage_Number_Person,&nbsp;Percentage_Zip_Code_Person&nbsp;and&nbsp;Percentage_City_Person&nbsp;respectively as street, number, zip code and city&nbsp;The&nbsp;Stacked bar chart should look more or less like this:&nbsp;&nbsp;Who can help me here?ThanksR.W.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

anonymous · Accepted Answer

Hi,&nbsp;I found the solution myself.I don't need to use measures that split up the percentages already on the level of type, as I use type on the X&nbsp;axis.&nbsp;It's enough to have the percentages per part.Percentage_Street =&nbsp;CALCULATE( [Percentage_KPI_ADDRESS]; F_KPI_ADDRESS[part] = "street" )Percentage_Number =&nbsp;CALCULATE( [Percentage_KPI_ADDRESS]; F_KPI_ADDRESS[part] = "number" )Percentage_Zip_Code =&nbsp;CALCULATE( [Percentage_KPI_ADDRESS]; F_KPI_ADDRESS[part] = "zip code" )Percentage_City =&nbsp;CALCULATE( [Percentage_KPI_ADDRESS]; F_KPI_ADDRESS[part] = "city" )These 4 measures I use as Values respecively as street, number, zip code and cityand on the Axis I use month and type.And drill down with&nbsp;&nbsp;&nbsp;&nbsp;The result is exactly what I wanted&nbsp;

Forum Discussion

Stacked bar chart with two different shared axis

2 Replies

Related content

Shared Semantic Model - Connectivity Issue from different tenants

Report Sharing

Different schema into different destination

Share Fabric Data Agent

Stacking Data Columns

date	number	number_OK	part	type	month	month_order
31/05/2020	109	98	street	person	May 2020	202005
31/05/2020	38	9	number	person	May 2020	202005
31/05/2020	285	264	zip code	person	May 2020	202005
31/05/2020	203	29	city	person	May 2020	202005
31/05/2020	51	27	street	organization	May 2020	202005
31/05/2020	64	48	number	organization	May 2020	202005
31/05/2020	70	47	zip code	organization	May 2020	202005
31/05/2020	37	30	city	organization	May 2020	202005
30/06/2020	33	26	street	person	June 2020	202006
30/06/2020	45	41	number	person	June 2020	202006
30/06/2020	243	60	zip code	person	June 2020	202006
30/06/2020	36	16	city	person	June 2020	202006
30/06/2020	26	4	street	organization	June 2020	202006
30/06/2020	247	176	number	organization	June 2020	202006
30/06/2020	93	41	zip code	organization	June 2020	202006
30/06/2020	266	191	city	organization	June 2020	202006
31/07/2020	126	4	street	person	July 2020	202007
31/07/2020	85	14	number	person	July 2020	202007
31/07/2020	212	151	zip code	person	July 2020	202007
31/07/2020	200	4	city	person	July 2020	202007
31/07/2020	134	46	street	organization	July 2020	202007
31/07/2020	192	60	number	organization	July 2020	202007
31/07/2020	206	6	zip code	organization	July 2020	202007
31/07/2020	181	30	city	organization	July 2020	202007
31/08/2020	283	194	street	person	August 2020	202008
31/08/2020	97	89	number	person	August 2020	202008
31/08/2020	189	53	zip code	person	August 2020	202008
31/08/2020	231	181	city	person	August 2020	202008
31/08/2020	294	5	street	organization	August 2020	202008
31/08/2020	124	109	number	organization	August 2020	202008
31/08/2020	307	75	zip code	organization	August 2020	202008
31/08/2020	210	27	city	organization	August 2020	202008