Add business unit to graph with cummulative sales

[Anonymous]

Hello,

I have made a graph to see a comparison of the cummulative sales for 2 years but now I would like to add the add the Business Unit as well, of which I have a slicer in my dashboard. So after adding this I will be able to see how the different business units are performing against each other and against last year. 

The original formula I am using is: 

Shipment value **bleep** = CALCULATE(SUM(Table1[Shipment value at A Price]),FILTER(ALLSELECTED(Table1),Table1[Fiscal Year] =max(Table1[Fiscal Year]) && Table1[Fiscal Week Number]<=max(Table1[Fiscal Week Number])))

Can anyone tell me how I can add the slicer Business Unit in here?

Thanks a lot!

Maartje

[v-yangliu-msft]

Hi  @Anonymous  ,

Does your date come from another table?

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data,we can better help you.

Best Regards,

Liu Yang

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

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous  ,

This is the data I created:

Here are the steps you can follow：

1. Create calculated column.

Year = YEAR('Table'[date])

Fiscal Week number = WEEKNUM([date],1)

Column = 'Table'[Year]&"-"&'Table'[Business unit]

Result:

2. Place [Fiscal Week number] on Axis, place [Column] on Legend, and place [amount] on Values.

3. Result:

Choose Year = 2020 and 2021. Choose Business unit = A and B

Choose Year = 2020 and 2021. Choose Business unit = A and B and C

Best Regards,

Liu Yang

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

[Anonymous]

@v-yangliu-msft 

I figured it out so no need to respond to my other question anymore. Thanks for your help!

regards Maartje

[Anonymous]

Thanks @v-yangliu-msft 

I did as you descibed above and it is working if I work with sales for each week. But I made a DAX to calculate the cumulative sales per week but then it isn't working. This is the formula for the cumulative sales: 

**bleep** ecom value = CALCULATE(SUM(Actuals[ value ecom ]),FILTER(ALLSELECTED(Actuals),Actuals[Fiscal Year] = max(Actuals[Fiscal Year]) && Actuals[Fiscal Week Number]<=max(Actuals[Fiscal Week Number]))).

Can you help me to get this graph also for cumulative sales?

Thanks a lot!

[Anonymous]

@v-yangliu-msft 

I tried this and it looks like I am getting closer. I do see the different business units now, but if I select more than 1 year, it only shows the latest year, so in this case 2021. So if I select only 2020 I get the right graph. Also, if I select only 2021 I get the right graph (with the right numbers).

But if I select both years, it shows 2021:

Besides that, the year numbers in the legend are quite strange. I only have 2019/2020/2021 in my years but it is named as 1905.

I used these formulas:

Year = YEAR(Table1[Fiscal Year])

Weeknumber = WEEKNUM(2019-01-01,2)

Column = 'Table1'[Year]&"-"&Table1[ Business unit ]

Hope you can help. Many thanks!

Regards, Maartje

[amitchandak]

@Anonymous =

Cumm =

CALCULATE(SUM(Table1[Shipment value at A Price]),FILTER(ALLSELECTED(Table1),Table1[Fiscal Year] =max(Table1[Fiscal Year]) && Table1[Fiscal Week Number]<=max(Table1[Fiscal Week Number]) && Table1[Business unit] =max(Table1[Business unit])))

Cumm till last year =

CALCULATE(SUM(Table1[Shipment value at A Price]),FILTER(ALLSELECTED(Table1),Table1[Fiscal Year] =max(Table1[Fiscal Year])-1 && Table1[Fiscal Week Number]<=max(Table1[Fiscal Week Number]) && Table1[Business unit] =max(Table1[Business unit])))

assumed [Fiscal Year] is a number

[Anonymous]

@amitchandak 

Thanks for your response.

If I do so I get the error message: Too few arguments were passed to the FILTER function. The minimum argument for the function is 2..

Any idea?

[amitchandak]

@Anonymous , I checked both codes, here they seem fine Check is a comma missing after 

FILTER(ALLSELECTED(Table1),

Or share your formula  from the file 

[Anonymous]

@amitchandak 

The formula is working now but I still don't get to see the result I want. I only see the total of the business units but I don't see them seperately. I had the same issue yesterday but then with the years. You helped me out as well I believe. Now I see the same result as I had with the first formula I started with. So if I select 1 business unit I see the sales for that business unit for both years. If I select multiple business units I see a sum of those business units but I would like to see them seperately. Below is the result I want to get (but this is made in excel).

But this is what I get now in power BI:

Can you help?