Combining 2 Columns together into single axis on a Column Chart

[Anonymous]

Hello,

I'm working with some data creating by simulating some numbers, and having trouble creating visuals around them.

I have two datasets: One of which shows current numbers (as of now). Another, which is a consolidation of numbers from different scenario runs run in the past)

For Example:

Dataset #1: 

CustomerID	Base Rating
123	RT1
456	RT2
789	RT2

Dataset #2:

CustomerID	Base Rating	Rating_Following_Simulation	Scenario
123	RT3	RT9	Scenario1
456	RT5	RT6	Scenario1
789	RT9	RT10	Scenario1
123	RT10	RT10	Scenario2
456	RT1	RT3	Scenario2
789	RT4	RT2	Scenario2

Effectively, Base Rating and Rating_Following_Simulation run on the same type of Ratin System.

I have Appended the two datasets so that they currently look like this:

CustomerID	Base Rating	Rating_Following_Simulation	Scenario
123	RT1		Baseline
456	RT2		Baseline
789	RT2		Baseline
123	RT3	RT9	Scenario1
456	RT5	RT6	Scenario1
789	RT9	RT10	Scenario1
123	RT10	RT10	Scenario2
456	RT1	RT3	Scenario2
789	RT4	RT2	Scenario2

Is there any for me to create a Column Chart, whereby the Base Rating and Rating Following Simulation can be combined and be shown in the same X-AXIS, without having to utlise drilldown? This way, a user can look at the rating and compare the rating as it currently is (baseline), and against the base rating when Scenario 1 was run, and then against the updated rating following Scenario 1 being run?

I want to then use Customer ID as the Y-AXIS to effectively Count Distinct how many Customer IDs fall into each of the Rating baskets.

Unsure on how to approach this. Would it be easier to Join? Or continue to Union the datasets?

[amitchandak]

@Anonymous , not very clear to me,

Check

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

[Anonymous]

@amitchandak 

Essentially, I have the #1 Dataset which is refreshed daily, and #2 Dataset which is built off the first dataset but based on simulations done to "shock" or "stress" the ratings. These shocks or stress, you can imagine them as a way of testing for extreme financial situations as an example. These ratings determine how much each customer would receive as an example. The

Now the results of #1 Dataset show the Base Rating as it is today (as it is refreshed daily).

Now the results off #2 Dataset show the Base Rating as it was when the simulations were run at the moment scenarios #1 and scenario #2 were run, and then after scenarios wer run - there new ratings.

I want to create a column chart which shows something similar to this - but Orange Bar would for example represent only the Base Rating from Dataset 1, and Dataset 2 would be represented on the Light Blue / Dark Blue as both the "Base Rating" for Scenario 1 and "Rating following Simulation".

X-Asis would me something like: RT 1, RT 2, RT3 etc. 

By extension, I'm hoping to build off a Slicer which allows me to change between scenarios 1 and 2 for the light and dark blue.

[v-lionel-msft]

Hi @Anonymous ,

You need to append these two tables together.

At last, you will get a table like this.

And Create a measure like this.

Measure = 
CALCULATE(
    DISTINCTCOUNT(Append2[CustomerID]),
    ALLEXCEPT(Append2, Append2[Rating1] )
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-lionel-msft That doesn't seem to help as I need a way to visualise the "Rating following Simulation" as well

Essentially there are 3 Legends:

1) Base Rating (No Scenario)

2) Base Rating (Scenario 1)

3) Rating Following Simulation (Scenario 1)

I need to build a slicer, so that Scenarios can also be changed (which will change the results for 2) and 3)).

[v-lionel-msft]

Hi @Anonymous ,

I don’t particularly understand the visualization you want, but maybe we can help you get the data table you want.

 You already have a table like the one above, What kind of table do you want to get to create visualization?

If you can provide a sample result table, maybe we can help you get it.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.