Log In
Register
Home
March 4, 2020
Offline
Hi All,
I have a fact data as per below (left) , I wanna build a Power bi dashboard as per below (right)
Client Amt Client Amt
A 100 A 100
B 300 C 500
C 500 Others 1200
D 700
E 200
Any input is much appreciated.
July 16, 2010
Offline
Hi Vivian,
Create a dimension table that maps the clients to their respective groups e.g.
Client Group
A A
B B
C Other
D Other
E Other
Then create a relationship between your fact and dim tables. Then create your visuals using the Group field.
Mynda
March 4, 2020
Offline
thanks Mynda, is there any alternative way to do it? We have thousands of clients and 10 clients are on watch list and rest will be shown as others.
And the client data will be grown from time to time.
Regards
July 16, 2010
Offline
Hi Vivian,
It should be fairly straight forward. Using Power Query, extract a list of distinct client names from your fact table. This table will become your dim table.
If you have the 10 'watch' clients listed in a separate table, you can use an 'if' statement to check if the name on each row of your dim table matches the name in the watch list table, if so, return their name, if not return 'other'. Then you will have a dimension table classified into those you want to watch and those that are 'other'.
Alternatively, you can add a conditional column to your new dim table and add each watch client as a separate criteria. e.g. if [client] = "Client A" then "Client A, else if [client] = "Client B" then "Client B" .... else "Other".
Hope that makes sense.
Mynda
1 Guest(s)
