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

[Anonymous]

Hello,

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

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.

[Anonymous]

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 :

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

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

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

Thanks for your help.

[Anonymous · Accepted solution]

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