Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Divide two dynamical Measures

Hello,

 

I have a P&L Table, sorted by Month.

P&L Tabel

 

I created the following measures to visualize the data: (note there are Slicers)

 

  1. Measure "Actual" just gives the values, according to category and month.
  2. Measure "Numerator" states the costs/incomes in the selected month. 

 

Numerator = 
VAR Cost_of_Material = CALCULATE([Actuals]; 
    FILTER('Financial Category'; 'Financial Category'[Category] = "Cost of Material"))
VAR Personnel = CALCULATE([Actuals]; 
    FILTER('Financial Category'; 'Financial Category'[Category] = "Personnel"))
VAR other_Exp = CALCULATE([Actuals]; 
    FILTER('Financial Category'; 'Financial Category'[Category] = "other op. Exp."))
VAR other_Inc = CALCULATE([Actuals]; 
    FILTER('Financial Category'; 'Financial Category'[Category] = "other op. Inc."))
VAR Depreciation = CALCULATE([Actuals]; 
    FILTER('Financial Category'; 'Financial Category'[Category] = "Depreciation"))

RETURN
(Cost_of_Material + Personnel + other_Exp + Depreciation + other_Inc)

 

     3. Measure "Total Output" gives the total Sales in the selected month.

 

Total Output = 
    CALCULATE([Actuals]; 
    FILTER('Financial Category'; 'Financial Category'[Category] = "Sales Revenue"))

 

      4. Measure "% of Sales Revenues" should give the percentage of each Numerator divided by Total Output.

 

% of Sales Revenue = 
[Numerator]/[Total Output]) // or DIVIDE([Numerator];[Total Output];0)

 

 

As you can see, the 4. Measure isn't showing my anticipated results. If I enter the static number of Sales Revenues (in the table = 4,836 Mil. €), I receive the correct percentages in the column. 

I tried several approaches to get the percentage. It seems that I can't put a measure, which only refers to one value, in the Denominator. I won't be able to add the Total Output manually in the Denominator using IF-clauses, since the table will update regularly.

 

Is there any way to divide the Nominators by Sales Revenue??

 

Thanks in advance

  • Perhaps try:

    Total Output =
    CALCULATE([Actuals];
    FILTER(ALL('Financial Category'); 'Financial Category'[Category] = "Sales Revenue"))

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    Perhaps try:

    Total Output =
    CALCULATE([Actuals];
    FILTER(ALL('Financial Category'); 'Financial Category'[Category] = "Sales Revenue"))
    • Anonymous's avatar
      Anonymous
      Not applicable

      Thanks a lot, it works! Finally!