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
RMV
Helper V
Helper V

calculate function with a static filter and a dynamic filter

Hi,

 

I have 2 figures: Revenue & Cost

The Cost can be break down to some categories.

Then I need to calculate the % cost amount of each categories per total revenue, where the amount is dynamically changed when Month slicer is changed. Can anyone help on this?

 

The table I have is a table with transactional data

Date           Transaction Type       Cost Category          Amount

1-Jan-17      Cost                              Cost 1                        1000

1-Jan-17      Revenue                                                         1500

2-Jan-17      Cost                              Cost 2                         300

3-Jan-17      Cost                              Cost 3                         400

5-Jan-17      Revenue                                                          2000

2-Feb-17     Cost                              Cost 1                        1000

2-Feb-17     Revenue                                                          1500

5-Feb-17     Cost                              Cost 3                         500

etc

The other table I have is Date Table.

 

The result I need looks like below

Month slicer: Jan - Feb

Cost Category         % Cost/Total Revenue

Cost 1                         40% ((1000 + 1000) / (1500 + 2000 + 1500))

Cost 2                         10% (500 / (1500 + 2000 + 1500))

Cost 3                         18% ((400 + 500) / (1500 + 2000 + 1500))

This figure can be changed depends on the Month slicer selected.

 

So, I'm thinking that when I create a Matrix Preview with Cost Category as row, I can get the total amount of each cost category. This figure will definitely changed, when I changed the Month slicer.

Then, I need to get the total revenue, with a static filter Transaction Type = "Revenue", and this figure needs to be changed when the Month slicer is changed. Then I can use a simple division calculation to get the percentage. However, I'm facing a difficulty to get the Total Revenue figure. The formula I have now is:

Total Revenue = CALCULATE(SUM(Table[Amount_),FILTER(DateCalendar,AND(MIN(DateCalendar[Date]),MAX(DateCalendar[Date]))),Table[Transaction Type]="Revenue")

This formula give me Total Revenue in the period applied in the Month slicer, however it doesn't retrieve Total Revenue in each of Cost Category rows, thus the % figure is not calculated well.

 

Can anyone help, please?

2 ACCEPTED SOLUTIONS
cs_skit
Resolver IV
Resolver IV

I think you should extract the Revenue into a second table with PowerQuery (aka "Query Editor")

 

Then the aggregation by Cost Type should immediately work. Reason it does not work that easily now is that your revenue data in same table is messing that up.

View solution in original post

v-huizhn-msft
Employee
Employee

Hi @RMV,

As the @cs_skit posted, you should put revenue values in a new table. I try to reproduce your scenario and get expected result.

1. Create a calculated column to get the month column using the formula.

Month = FORMAT(Test[Date],"MMM")

1.PNG

2. Create a new table including venune value by clicking "New Table" under Modeling on home page, type the formula.

 

Table = FILTER(Test,Test[Transaction]="revenue")

3.png

3. Create a new table to get distinct month.

NewMonth = DISTINCT(Test[Month])


4.PNG

4. Create relationship between the three tables.

2.PNG

5. Create a slicer including NewMonth[Month], create measure using the formula:Percentage = SUM(Test[Amount])/SUM('Table'[Amount])

Please see the following expected result.

 

6.PNG7.PNG

Best Regards,
Angelia

View solution in original post

3 REPLIES 3
v-huizhn-msft
Employee
Employee

Hi @RMV,

As the @cs_skit posted, you should put revenue values in a new table. I try to reproduce your scenario and get expected result.

1. Create a calculated column to get the month column using the formula.

Month = FORMAT(Test[Date],"MMM")

1.PNG

2. Create a new table including venune value by clicking "New Table" under Modeling on home page, type the formula.

 

Table = FILTER(Test,Test[Transaction]="revenue")

3.png

3. Create a new table to get distinct month.

NewMonth = DISTINCT(Test[Month])


4.PNG

4. Create relationship between the three tables.

2.PNG

5. Create a slicer including NewMonth[Month], create measure using the formula:Percentage = SUM(Test[Amount])/SUM('Table'[Amount])

Please see the following expected result.

 

6.PNG7.PNG

Best Regards,
Angelia

Hi @cs_skit & @v-huizhn-msft,

 

I modified the scheme a bit, by still using Date table, instead of creating another table for Month.

This is also to keep the flexibility design, in case we need to break down with other period of time, for example week or others.

Anyway, the scheme you both mentioned works really well, and I got the correct figure I need now.

 

Thanks a lot!

cs_skit
Resolver IV
Resolver IV

I think you should extract the Revenue into a second table with PowerQuery (aka "Query Editor")

 

Then the aggregation by Cost Type should immediately work. Reason it does not work that easily now is that your revenue data in same table is messing that up.

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.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors