DAX formula for allocation ratio denominator

[jkapso751]

Hi all,

I’m trying to create a measure that will calculate the same number for each row in the pivot.  The measure will be used as the denominator for an allocation ratio.  The measure should be sum of CATEGORY1 and CATEGORY2 revenue for each COMPANY CODE.

DATA is my fact table that has company codes, accounts, and amounts (among other things).

Revenue Measure = CALCULATE(sum(DATA'[Amount]),'DATA'[Account]="Revenue Account Code")

Revenue Allocation Key Denominator =

if([Revenue Measure]=0,BLANK(),

CALCULATE(sum('DATA'[Amount]),

ALLEXCEPT('DATA','DATA'[COMPANY CODE]),

'DATA'[Account]="Revenue Account Code",

'DATA'[CATEGORY]="CATEGORY1"||'DATA'[CATEGORY]="CATEGORY2"))

Desired output:

The formula above works, but it populates the value for all categories (which is fine, since the numerator is zero for those rows).  I have two questions:

• Is there a better way to write this formula, perhaps one that makes the “if([Revenue Measure]=0,BLANK()” unnecessary?
• If I wanted to write this formula so that the amounts are only populated for the CATEGORY1 and CATEGORY2 rows, how could I do that?

Many thanks for your help!!!

[tamerj1]

Hi @jkapso751 

please try

Revenue Allocation Key Denominator =
CALCULATE (
SUM ( 'DATA'[Amount] ),
ALLEXCEPT ( 'DATA', 'DATA'[COMPANY CODE] ),
'DATA'[Account] = "Revenue Account Code",
KEEPFILTERS ( 'DATA'[CATEGORY] IN { "CATEGORY1", "CATEGORY2" } )
)

[jkapso751]

Thanks tamerj1.  Tried the formula, but the measure is still populating all the rows in the table including the ones with no revenue.