powerbidax

dax measure percentage of cost

i am trying to get the cost percentage over the revenue, these are my fact and dims table
enter image description here

enter image description here
i currently have three measures to get the total cost, the revenue and the cost percentage,

Total_Cost = 
CALCULATE(
    SUM(fact_tablef[Value]),
    fact_table[Cost Element Id] IN VALUES(dims_table[Cost Element Id]),
    FILTER(
        fact_table,
        fact_table[Cost Element Id] IN VALUES(dims_table[Cost Element Id])

Revenue = 
CALCULATE(
    SUM(fact_table[Value]),
    fact_table[Cost Element Id] = "Revenue"
)

Percentage = DIVIDE([Total_Cost],[Revenue],0)

the problem is the percentage shows just fine when its the grand total cost, but when i select the cost category or the cost element the percentage shows 0.

are there any issues with my calculation, are there any solution to fix this?
thanks!

Edit: Sample Data (i'll be using whole numbers for the sake of simple calculation) (only including the columns that are used)

Workcell Cost Element Id Cost Element Name Value
Customer 1 Revenue Revenue 1000
Customer 1 00002 Pedals 200
Customer 1 00003 Headlights 100
Cost Category Cost Element Id Cost Element Name
Components 00002 Pedals
Components 00003 Headlights

using the measures above, the desired output would be

percentage = 30% no slicer applied, total of all cost (this works fine)

percentage = 30% workcell slicer applied(Customer 1 selected) (this also works fine)

percentage = 30% cost category slicer applied (Components selected) this does not work, shows 0%

percentage = 10% cost element id slicer applied (00003 selected) this also doesn't work, shows 0%

Solution

  • Since you have a relationship between the two, your Revenue measure is returning null when the dims_table is filtered. You will need to "remove" the relationship with either of these two options:

    Using REMOVEFILTERS:

    Revenue = 
  CALCULATE(
    SUM(fact_table[Value]),
    fact_table[Cost Element Id] = "Revenue",
    REMOVEFILTERS(dims_table)
  )

    Using CROSSFILTER:

    Revenue = 
  CALCULATE(
    SUM(fact_table[Value]),
    fact_table[Cost Element Id] = "Revenue",
    CROSSFILTER(dims_table[Cost Element Id], fact_table[Cost Element Id], None)
  )