calculate function with a static filter and a dynamic filter

[RMV]

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?

[cs_skit · Accepted solution]

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.

[v-huizhn-msft · Accepted solution]

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")

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. Create a new table to get distinct month.

NewMonth = DISTINCT(Test[Month])

4. Create relationship between the three tables.

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.

Best Regards,
Angelia

[RMV]

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 · Accepted solution]

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.