Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Add business unit to graph with cummulative sales

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

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @Anonymous  ,

This is the data I created:

v-yangliu-msft_0-1620969282228.png

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:

v-yangliu-msft_1-1620969282309.png

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

v-yangliu-msft_2-1620969282311.png

3. Result:

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

v-yangliu-msft_3-1620969282314.jpeg

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

v-yangliu-msft_4-1620969282321.png

 

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.

View solution in original post

9 REPLIES 9
v-yangliu-msft
Community Support
Community Support

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
Community Support
Community Support

Hi  @Anonymous  ,

This is the data I created:

v-yangliu-msft_0-1620969282228.png

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:

v-yangliu-msft_1-1620969282309.png

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

v-yangliu-msft_2-1620969282311.png

3. Result:

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

v-yangliu-msft_3-1620969282314.jpeg

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

v-yangliu-msft_4-1620969282321.png

 

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
Not applicable

@v-yangliu-msft 

 

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

regards Maartje

Anonymous
Not applicable

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
Not applicable

@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).

 

Maartjevd_0-1621234286141.png

Maartjevd_1-1621234429326.png

But if I select both years, it shows 2021:

Maartjevd_2-1621234569965.png

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 ]
Maartjevd_3-1621234707050.png

Hope you can help. Many thanks!

Regards, Maartje

 

 

amitchandak
Super User
Super User

@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
Not applicable

@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?

@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
Not applicable

@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).

Maartjevd_0-1620832553993.png

 

But this is what I get now in power BI:

Maartjevd_1-1620832654076.png

Can you help?

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.