Formula Assist DAX

[Sonova]

Hi

I am trying to calculate a measure which will take my cost category as a percentage of revenue. This is done with the 2 measures below which do not show any errors.

Monthly revenue Calculation

Revenue Calc:=calculate(sum([Actual]),filter(Categories,Categories[Category]="9"))

As a percentage of revenue.

% Of Revenue:=calculate(divide(sum([Actual]),[Revenue Calc],0),ALL(Categories[Category],Categories[Category]))

I have a pivot table to material, I would like to see the material cost % proportionate to revenue for the month, however when I add the % Of Revenue measure it does not show anything. It seems that because the pivot table is filtered to material my revenue is not brought in which results in 0% calculation.

https://www.dropbox.com/s/7jd13soh28euohz/FormulaAsssist.PNG?dl=0

 

[Matt Allington]

I'm not sure I fully understand, but I can tell you that this formula is semantically flawed

[COLOR=#333333]Revenue Calc:=calculate(sum([Actual]),filter(Categories,Categories[Category]="9"))[/COLOR]

The issue is you are applying a new filter on Categories[Category] which is added to any other filter already on this column. I would think this would cause a problem. Try this instead

[COLOR=#333333]Revenue Calc:=calculate(sum([Actual]),filter(ALL(Categories),Categories[Category]="9"))[/COLOR]

 

[FranzV]

I agree with Matt, you need to remove the filters from the Categories table before applying the new filter to show revenue only. You can change your measures to:

Revenue Calc :=
CALCULATE (
    [Sum Actual],
    FILTER ( ALL ( Categories ), Categories[CategoryKey] = 38 )
)

% Of Revenue :=
DIVIDE ( [Sum Actual], [Revenue Calc] )

 

[Sonova]

Thanks your recommendations worked.