Excel to Power Bi two axis and grand total help

Anonymous 

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