Excel to Power Bi two axis and grand total help

[luisClive]

Hi All,

I have the excel below, i want to replicate on Power Bi

Data Set is below

	FY21	FY20	FY19
	UAE	ME	Nationals	UAE	ME	Nationals	UAE	ME	Nationals
Assurance	39	155	8	40	154	12	47	170	5
Consulting	39	84	6	44	98	5	39	81
Deals	8	24	3	14	34	5	11	42	2
TLS	6	25	2	13	31	3	4	19	0
Total	92	288	19	111	317	25	101	312	7

after pivoting and unpivoting for Bi, i managed to get just one axis

I need two axis and grand total

LOS	ME -UAE -Nationals	Sort	Headcount	FY
Assurance	ME	1	155	FY21
Assurance	UAE	2	39	FY21
Assurance	Nationals	3	8	FY21
Consulting	ME	1	84	FY21
Consulting	UAE	2	39	FY21
Consulting	Nationals	3	6	FY21
Deals	ME	1	24	FY21
Deals	UAE	2	8	FY21
Deals	Nationals	3	3	FY21

[PaulDBrown]

@luisClive 

It takes time to get out of the "Excel" mindset...!

Let us know if you get stuck anywhere

[luisClive]

@PaulDBrown 

Hi Paul

would you be kind enough to recheck this solution, which I use a lot

after all the new updates to PBI, once i remove items from Axis, Legend and Values well and then try to add the same back,

i cannot achieve below hierarchy any more

Either (ME, national....) or (FY...) shows up on X axis.

Both dont show up together

[PaulDBrown]

@luisClive 

Sorry I am not following you... Can you post an image of what you are getting?

[PaulDBrown · Accepted solution]

@luisClive 

Here is one way of doing this (I'll attach the sample PBIX file to the reply for you).

First the result:

This involves creating a disconnected table to use as the legend including the "total" for each category in the axis.

The process involves:

1) Create a new table using the LOS values and adding a row for "Total" (I created it using the "Enter Data" function in the ribbon):

2) Create a FY table with unique values by referencing a new query to the source table (to ensure that the periods update automatically) and pivot the columns

3) Append both these tables and unpivot the FY columns. The end result is:

4) Load into the model and do not create a relationship

5) Create the relevant measures:

Headcount sum = SUM('Table'[Headcount])

TREATAS LOS = CALCULATE([Headcount sum], TREATAS(VALUES('Legend Table'[LOS]), 'Table'[LOS]), TREATAS(VALUES('Legend Table'[FY]), 'Table'[FY]))

Total by LOS = CALCULATE([Headcount sum], TREATAS(VALUES('Legend Table'[FY]), 'Table'[FY]))

6) Now create the measure you will use in the actual visual:

Chart measure = IF(SELECTEDVALUE('Legend Table'[LOS]) = "Total", 
                    [Total by LOS], 
                        [TREATAS LOS])

7) and build the visual using the Legend Table field "Legend LOS" as the legend:

😎 finally format the X-axis to your liking.

Attached is the sample PBIX

[luisClive]

@PaulDBrown 

OMG, this is some serious wizardry. Appreciate it a lot.

Never though something so simple on excel could be so involving.

[luisClive]

@PaulDBrown 

Could you guide me?

[amitchandak]

@luisClive , FY as Axis and LOS as legend in clustered column visual

[luisClive]

@amitchandak 

Doing exactly that, i got the second BI visual i posted

However i want my final solution to be like the excel chart i posted ( first Visual)

[amitchandak]

@luisClive , Seem like you are looking for Hybrid display, There are a couple of Article on that

https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1...
https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/...

vote for Hybrid Table
https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebc

[luisClive]

thanks @amitchandak 

I was able to achieve matrix table visual im looking for by unpivoting, but I could do a hybrid/two axis chart like excel