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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

how to have column stacked charts and line chart combined into one ?

Hello,

I need to reproduce the following Excel charts in my Power BI, the excel can be found here : https://we.tl/t-wkNmNiaQGL ) :

jmclej2_0-1682344068473.png

Here is the pbix file : https://we.tl/t-yS7mPD7HzJ

If you cannot access the link, here is the M code :

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WysxLzkxJzSspVtJRMjQAEpZADKKNlWJ1opVSUnMT81JSQbKmIBUgwhzMAcmW5Jck5sCEjUygchZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SerieName = _t, LessThan1 = _t, Between1And3 = _t, Between3And5 = _t, GreaterThan5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"SerieName", type text}, {"LessThan1", Int64.Type}, {"Between1And3", Int64.Type}, {"Between3And5", Int64.Type}, {"GreaterThan5", Int64.Type}})
in
    #"Changed Type"

 

I don't know if it is possible or if I must have three different queries, one for each line of the actual table ?

I saw the "Line and stack column chart" visual that would seem to be OK, but I can't figure out how to set it.

Do you have any idea please ?

If there is a need to remodel the data, it's not a problem.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I came back with a solution with 1 table, like this I don't have the issue of the relationships anymore. What I had to do was just to transpose my data. But I then had an issue with the ordering of my X-axis. I first added an "order" column, but if a one column has more than similar value, it cannot have 2 different order number. In harder to not hardcode anything in the values, I added spaces in front of the Y-axis elements and it works fine. I didn't test but it seems I could have a generic way by adding a dax column like this : 

SortSchool = 
RANKX(
    'Table',
    CALCULATE(MIN('Table'[Grade]), ALLEXCEPT('Table', 'Table'[school])), , ASC, Dense
)

You can find my solution here (tab  : Page2) in a V3 : https://we.tl/t-jWEKY451bI 

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

I have tried the idea I had with 3 queries (I have extracted each line and unpivoted them) and it works (almost) perfectly. Here is the V2 of the pbix : https://we.tl/t-c2iHv1YpIo 

I get that :

jmclej2_3-1682359131505.png

 

I have 1 issue left, it automatically creates the following relationships :

jmclej2_4-1682359203039.png

I have tried to remove them because I don't think they are relevant, but then I don't get the correct charts anymore... :

jmclej2_5-1682359259148.png

So is there a way to remove those relationships and still have my charts displayed as expected ?

Thanks for your help.

Anonymous
Not applicable

I came back with a solution with 1 table, like this I don't have the issue of the relationships anymore. What I had to do was just to transpose my data. But I then had an issue with the ordering of my X-axis. I first added an "order" column, but if a one column has more than similar value, it cannot have 2 different order number. In harder to not hardcode anything in the values, I added spaces in front of the Y-axis elements and it works fine. I didn't test but it seems I could have a generic way by adding a dax column like this : 

SortSchool = 
RANKX(
    'Table',
    CALCULATE(MIN('Table'[Grade]), ALLEXCEPT('Table', 'Table'[school])), , ASC, Dense
)

You can find my solution here (tab  : Page2) in a V3 : https://we.tl/t-jWEKY451bI 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors