Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Stacked bar chart with two different shared axis

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

datenumbernumber_OKparttypemonthmonth_order
31/05/202010998streetpersonMay 2020202005
31/05/2020389numberpersonMay 2020202005
31/05/2020285264zip codepersonMay 2020202005
31/05/202020329citypersonMay 2020202005
31/05/20205127streetorganizationMay 2020202005
31/05/20206448numberorganizationMay 2020202005
31/05/20207047zip codeorganizationMay 2020202005
31/05/20203730cityorganizationMay 2020202005
30/06/20203326streetpersonJune 2020202006
30/06/20204541numberpersonJune 2020202006
30/06/202024360zip codepersonJune 2020202006
30/06/20203616citypersonJune 2020202006
30/06/2020264streetorganizationJune 2020202006
30/06/2020247176numberorganizationJune 2020202006
30/06/20209341zip codeorganizationJune 2020202006
30/06/2020266191cityorganizationJune 2020202006
31/07/20201264streetpersonJuly 2020202007
31/07/20208514numberpersonJuly 2020202007
31/07/2020212151zip codepersonJuly 2020202007
31/07/20202004citypersonJuly 2020202007
31/07/202013446streetorganizationJuly 2020202007
31/07/202019260numberorganizationJuly 2020202007
31/07/20202066zip codeorganizationJuly 2020202007
31/07/202018130cityorganizationJuly 2020202007
31/08/2020283194streetpersonAugust 2020202008
31/08/20209789numberpersonAugust 2020202008
31/08/202018953zip codepersonAugust 2020202008
31/08/2020231181citypersonAugust 2020202008
31/08/20202945streetorganizationAugust 2020202008
31/08/2020124109numberorganizationAugust 2020202008
31/08/202030775zip codeorganizationAugust 2020202008
31/08/202021027cityorganizationAugust 2020202008


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

Percentage_KPI_ADDRESSROUND( DIVIDE( SUM(F_KPI_ADDRESS[number_OK]); SUM(F_KPI_ADDRESS[number]) ); 4 )
Percentage_Street_OrganizationCALCULATE( [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_OrganizationPercentage_Number_OrganizationPercentage_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_PersonPercentage_Number_PersonPercentage_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:
 
KPI_ADDRESS.jpg
 
Who can help me here?
Thanks

R.W.
 
 
 
 
 
 
 
 
 
 
 
 
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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_StreetCALCULATE( [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 
DrillDown.jpg

 

 

 

The result is exactly what I wanted

 

KPI_ADDRESS.jpg

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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_StreetCALCULATE( [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 
DrillDown.jpg

 

 

 

The result is exactly what I wanted

 

KPI_ADDRESS.jpg

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors