Visualise percentage of sales over time Power Bi for each salesman

Question

I have a dataset that looks like.

+------------+-------+----------+--+
|    Date    | Sales | Salesmen |  |
+------------+-------+----------+--+
| 12/31/1999 |   100 | P1       |  |
| 12/31/1999 |   100 | P2       |  |
| 12/31/1999 |   300 | P3       |  |
| 01/31/2000 |   500 | P2       |  |
| ...        |       |          |  |
| 07/31/2020 |   500 | p3       |  |
+------------+-------+----------+--+

But I want to visualise this as a line chart (with multiple lines for each salesmen), as a percentage of the total sales per salesman per year and visualise it over the full 20 year period(1999-2020)yearly.

Power BI for visualising lines has 3 fields: Axis , Legend and Values. I placed date in axis, Salesmen in legend and for values I created a measure of the Sales(displayed "As a % of Grand total" option), however it doesn't return what I would like it to.

Measure = CALCULATE(SUM('Table1'[Sales]),FILTER('Table1','Table1'[Date].[Year]=2020))

However this only returns percentage values of the sum total for 1 year.

and the visualisation outputs a table that looks like.

So I used another measure

Measure = SUMX(CALCULATETABLE('Table1','Table1'[Date].[Year]), 'Table1'[Sales])

however this reverts the same value for each year.

However this produces a table that looks like:

I need it as a sum total of sales per year split as percentage among the 3 salesmen, then visualised. So I know there's something I'm missing in the measure, any suggestions?

mkRabbani mkRabbani · Accepted Answer · 2020-08-25T04:33:25

Follow these following step-

Step-1: Create a measure to calculate total sales as below-

total_sales = SUM(Table1[sales])

Step-2: Create a measure that will calculate SUM of amount per year as below-

current_year_sales = 

var current_row_year = MIN(Table1[Date].[Year])

return 
CALCULATE(
    SUM(Table1[sales]),
    FILTER(
        ALL(Table1),
        Table1[Date].[Year] = current_row_year 
    )
)

Step-3: Now create the final measure as below. Convert this measure type to %

year_wise_percentage = [total_sales]/[current_year_sales]

Spet-4: Configure your line chart as below-

Axis: Year
Legend: salesmen
Values: year_wise_percentage

You should have your expected output now.

Visualise percentage of sales over time Power Bi for each salesman

1 Answers