% share of revenue calculation filter issue

[woombers]

Hi there

Struggling to figure out how to apply filters on this use-case. My goal is to populate the "% share of revenue" columns in the Cost of Materials and Personnel Costs columns below.

	Revenue		Cost of Materials		Personnel costs
Product	Value	% share of Revenue	Value	% share of Revenue	Value	% share of Revenue
Product 1		100%		?%		?%
Product 2		100%		?%		?%
Product 3		100%		?%		?%
Product 4		100%		?%		?%
Product 5		100%		?%		?%

The data comes from a budget fact table linked to a product table and an account group (top row in the table above) table. To make matters more intersting, I also have a slicer with a budget version on the dashboard which should ideally still work 8the fact table contains multiple iterations of the budget).

I have two measures - one for the value which is easy enough SUM(budget[Budget_Value] - and the second for the percentage which is where my problem is. These are my attempts so far:

Share of Revenue = SUM(budget[Budget_Value]) / CALCULATE(SUM(budget[Budget_Value]),bi_pl_accountgroup[p&l_account-group-EN] = "Revenue")

Result: budget value is filtered on the account group and the costs therefore can't be divided by the revenue (0)

Share of Revenue = SUM(budget[Budget_Value] / CALCULATE(SUM(budget[Budget_Value]), ALLSELECTED(),bi_pl_accountgroup[p&l_account-group-EN] = "Revenue")

Result: the account group filter is ignored so the costs on the account groups can be divided by the revenue BUT the product filter is now ignored as well meaning that the comparison only works on the column total.

Any help on how to allow the row level but not the column level filter? 

I imagine I'm probably not setting up the data in the correct way to do this and/or missing out on some basics so any help would be much appreciated!

[tamerj1]

Hi @woombers 

Would please share a screenshot of you data model? What is the type and filter direction between the tables?

thank you

[woombers]

Hi Tamerj

These are the tables. I simplified the naming for the purpose of the message above but basically One to Many with single cross-filter direction for both relevant relationships.

[tamerj1]

Hi @woombers 
You need to remove the filter comming from the bi_pl_accountgroup[p&l_account-group-EN] column. Try this:

Share of Revenue =
SUM ( budget[Budget_Value] )
    / CALCULATE (
        SUM ( budget[Budget_Value] ),
        REMOVEFILTERS ( bi_pl_accountgroup[p&l_account-group-EN] ),
        bi_pl_accountgroup[p&l_account-group-EN] = "Revenue"
    )

 Please let me know if this solves your problem. If so, kindly mark my reply as accepted soluation. Thank you!

[woombers]

I'm afraid this has the same result as the second measure detailed above. The filter is removed but then added again so that the division of the costs is not calculated vs the revenue

[tamerj1 · Accepted solution]

try removing the filter from the table completely:

REMOVEFILTERS ( bi_pl_accountgroup )

[woombers]

That did the trick. Within the measure:

>> Filters removed from the table with REMOVEFILTER

>> Filter reset on the column within the table using the normal CALCULATE

Thanks so much for your help. I've spent an embarassing amount of time trying to figure this out

[tamerj1]

Most welcome dear.

Please let me know if you seek any further help.

Have a great Day!
Tamer

[tamerj1]

Otherwise try this

Share of Revenue =
DIVIDE (
    SUM ( budget[Budget_Value] ),
    CALCULATE (
        SUM ( budget[Budget_Value] ),
        FILTER (
            ALL ( bi_pl_accountgroup ),
            bi_pl_accountgroup[p&l_account-group-EN] = "Revenue"
        )
    )
)

[tamerj1]

Also let me add that it is safer in all cases to protect your code using DIVIDE function instead of "/" operator incase you have zeros at the denominator.