Forum Discussion

GellaiTamas's avatar
GellaiTamas
Icon for Helper I rankHelper I
9 years ago
Solved

DAX_Filtered division within one column

Hi,

 

I have many columns in an SQL DB table and these 2 among them: [Property] and [Value].  [Property] has ~30 different items.

How can I divide only 2 specific properties? I'm not quite sure how an SQL WHERE clause could be translated into DAX. I've tried this measure without success:

 

InflationPerCostBase = CALCULATE(
	DIVIDE(
		SUM('Report1 (2)'[Value]);
		SUM('Report1 (2)'[Value])
	);
	FILTER(
		'Report1 (2)';
		'Report1 (2)'[Property] = "AOP Inflation" &&
		'Report1 (2)'[Property] = "AOP Cost Base" 
	)
)

 

What is the best practice here?

 

Thanks,

Tamás

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    GellaiTamas,

     

    Hi Tamás,

     

    We can evaluate the totals separately. Try this formula please.

    InflationPerCostBase =
VAR AOPInflation =
    CALCULATE (
        SUM ( 'Report1 (2)'[Value] );
        'Report1 (2)'[Property] = "AOP Inflation"
    )
VAR AOPCostBase =
    CALCULATE (
        SUM ( 'Report1 (2)'[Value] );
        'Report1 (2)'[Property] = "AOP Cost Base"
    )
RETURN
    DIVIDE ( Inflation; CostBase; 0 )

    Best Regards!

    Dale

2 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    GellaiTamas,

     

    Hi Tamás,

     

    We can evaluate the totals separately. Try this formula please.

    InflationPerCostBase =
VAR AOPInflation =
    CALCULATE (
        SUM ( 'Report1 (2)'[Value] );
        'Report1 (2)'[Property] = "AOP Inflation"
    )
VAR AOPCostBase =
    CALCULATE (
        SUM ( 'Report1 (2)'[Value] );
        'Report1 (2)'[Property] = "AOP Cost Base"
    )
RETURN
    DIVIDE ( Inflation; CostBase; 0 )

    Best Regards!

    Dale