Chart - Clustered Column and line

[mohassan99]

I have this clustered column & line chart.

I want two values on the horizontal axis, but there's one.

The issue with it is clearer, even to me, when I show it as a table.

If show as table there's only one row. I think there should be two..

This is how it looks as a table:

Table view.

What I think I want/need is 2020 in one row and 2021 in another.

The bottom line is the question is how to I reorganize the data so that it is in two rows with 2020 in one row and 2021 in the other? 

The reason I want them in two rows is because I want 2020 and 2021 to be all I have on the x axis. I want "Earnings" and "Potential Earnings" on the one vertical axis as a columns and "Percent of Potential Earned" on the other vertical as a line.

Data Model:

Each of these is from a different table. There are three one-way relationships between the tables. That’s …because each table’s primary key is a cluster of these 3 (ProviderID, AddressID, and IncentiveProgram)

Data Model.

[jsaunders_zero9 · Accepted solution]

@mohassan99 

Is this what you are trying to acheive?

Couple of things, Is there any reason that you can't append the you two tables together, they look identical.

Here is a link to a file that I put together to show you what I mean - mohassan99_sample.pbix.

I would also suggest you looking into some basic data modelling concepts in Power BI, I re read your first question about your data model and three relationships - relationships don't work how you think they do.

Thank you

[jsaunders_zero9]

Hi @mohassan99 

I would suggest introducing a calendar table and then summing your measures as totals, can you provide some dummy data and I can m show you how.
Thank you

[mohassan99]

Do you mean a date table? PowerBI says:

A date table is a table that meets the following requirements:

• It must have a column of data type date (or date/time)—known as the date column.
• The date column must contain unique values. (Not the case.)
• The date column must not contain BLANKs.
• The date column must not have any missing dates.
• The date column must span full years. A year isn't necessarily a calendar year (January-December).
• The date table must be marked as a date table.

The data looks like:

Query/Data Source 1

Year	Provider	Value1
2020	1	10
2020	1	10
2020	2	20
2020	2	20

Query/Data Source 1

Year	Provider	Value1
2021	1	30
2021	1	30
2021	2	40
2021	2	40

[jsaunders_zero9 · Accepted solution]

@mohassan99 

Is this what you are trying to acheive?

Couple of things, Is there any reason that you can't append the you two tables together, they look identical.

Here is a link to a file that I put together to show you what I mean - mohassan99_sample.pbix.

I would also suggest you looking into some basic data modelling concepts in Power BI, I re read your first question about your data model and three relationships - relationships don't work how you think they do.

Thank you

[mohassan99 · Accepted solution]

I think I may have to append the tables in a query. Apparently a data relationship is not a join so, yeah, I wasn't sure what they were. 

Thank you,

Mo

[jsaunders_zero9]

Spot on mate, best to append those in PQ before bringing into PBI