Forum Discussion

RMV's avatar
RMV
Helper V
9 years ago
Solved

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 dynamica...
Need Help
  • cs_skit's avatar
    cs_skit
    9 years ago

    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's avatar
    v-huizhn-msft
    9 years ago

    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

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

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's avatar
RMV
Helper V
9 years ago

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!