Stacked bar chart with two different shared axis

[Anonymous]

Hello,

I have the following table F_KPI_ADDRESS with the fields:
date : contains last date of months
number: contains the number of records in that month, for a certain part and a certain type 

number_OK: contains the number of records in that month, for a certain part and a certain type that meet the KPI

part: 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 month_order

month_order = FORMAT(F_KPI_ADDRESS[date]; "yyyymm")

F_KPI_ADDRESS

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

The following measures I made to represent in a Stacked bar chart:

Percentage_KPI_ADDRESS = ROUND( DIVIDE( SUM(F_KPI_ADDRESS[number_OK]); SUM(F_KPI_ADDRESS[number]) ); 4 )

Percentage_Street_Organization = CALCULATE( [Percentage_KPI_ADDRESS]; F_KPI_ADDRESS[part] = "street"; F_KPI_ADDRESS[type] = "organization" )

Percentage_Street_Person = CALCULATE( [Percentage_KPI_ADDRESS]; F_KPI_ADDRESS[part] = "street"; F_KPI_ADDRESS[type] = "person" )

Percentage_Number_Organization = CALCULATE( [Percentage_KPI_ADDRESS]; F_KPI_ADDRESS[part] = "number"; F_KPI_ADDRESS[type] = "organization" )

Percentage_Number_Person = CALCULATE( [Percentage_KPI_ADDRESS]; F_KPI_ADDRESS[part] = "number"; F_KPI_ADDRESS[type] = "person" )

Percentage_Zip_Code_Organization = CALCULATE( [Percentage_KPI_ADDRESS]; F_KPI_ADDRESS[part] = "zip code"; F_KPI_ADDRESS[type] = "organization" )

Percentage_Zip_Code_Person = CALCULATE( [Percentage_KPI_ADDRESS]; F_KPI_ADDRESS[part] = "zip code"; F_KPI_ADDRESS[type] = "person" )

Percentage_City_Organization = CALCULATE( [Percentage_KPI_ADDRESS]; F_KPI_ADDRESS[part] = "city"; F_KPI_ADDRESS[type] = "organization" )

Percentage_City_Person = CALCULATE( [Percentage_KPI_ADDRESS]; F_KPI_ADDRESS[part] = "city"; F_KPI_ADDRESS[type] = "person")

In the Stacked bar chart I want to see both F_KPI_ADDRESS[month] as F_KPI_ADDRESS[type] on the X axis
The Values for F_KPI_ADDRESS[type] = "organization": 
Percentage_Street_Organization, Percentage_Number_Organization, Percentage_Zip_Code_Organization and Percentage_City_Organization respectively as street, number, zip code and city

The stacked values for F_KPI_ADDRESS[type] = "person": 
Percentage_Street_Person, Percentage_Number_Person, Percentage_Zip_Code_Person and Percentage_City_Person respectively as street, number, zip code and city

The Stacked bar chart should look more or less like this:

Who can help me here?
Thanks

R.W.

[Anonymous · Accepted solution]

Hi,

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 axis. 
It's enough to have the percentages per part.

Percentage_Street = CALCULATE( [Percentage_KPI_ADDRESS]; F_KPI_ADDRESS[part] = "street" )

Percentage_Number = CALCULATE( [Percentage_KPI_ADDRESS]; F_KPI_ADDRESS[part] = "number" )

Percentage_Zip_Code = CALCULATE( [Percentage_KPI_ADDRESS]; F_KPI_ADDRESS[part] = "zip code" )

Percentage_City = CALCULATE( [Percentage_KPI_ADDRESS]; F_KPI_ADDRESS[part] = "city" )

These 4 measures I use as Values respecively as street, number, zip code and city

and on the Axis I use month and type.

And drill down with 

The result is exactly what I wanted

[v-yingjl]

Glad to hear that you have solved it by yourself and thanks for your sharing your solution that it could help others quickly! 

Best Regards,
Community Support Team _ Yingjie Li