How to calculate child amount sum when actual amout is null on some field

[praeepmaharathi]

How to calculate child amount sum when actual amout is null on some field. can you please help to provide the dax formula.

Managed - Combined(null Actual)= sum of managed actual value

Unmanaged - Combined(null Actual)=sum of unmanaged actual value

Data for reference

Country	U/M	Type	Target	Actuals
India	Managed	Managed - Combined		$1
India	Managed	Managed - Individual	$1	$5
India	Managed	Managed - Individual	$3	$3
India	Managed	Managed - Individual	$5	$4
India	Managed	Managed - Individual	$4	$2
India	Managed	Managed - Individual	$3	$1
India	Managed	Managed - Individual	$2	$1
India	Managed	Managed - Individual	$1	5
India	Managed	Managed - Individual	$2	1
India	Managed	Managed - Individual	$3	1
India	Managed	Managed - Individual	$4	2
India	Unmanaged	Unmanaged		$2
India	Unmanaged	Unmanaged		$1
India	Managed - Combined		$5
India	Unmanaged - Combined		$1

[v-xiaotang · Accepted solution]

Hi @praeepmaharathi 

you can try this, create the measure,

Measure = 
    var _UM=MIN('Table'[U/M])
    var _mTotal=CALCULATE(SUM('Table'[Actuals]),FILTER(ALL('Table'),'Table'[U/M]="Managed"))
    var _uTotal=CALCULATE(SUM('Table'[Actuals]),FILTER(ALL('Table'),'Table'[U/M]="Unmanaged"))
return SWITCH(TRUE(),
_UM="Managed - Combined",_mTotal,
_UM="Unmanaged - Combined",_uTotal,
MIN('Table'[Actuals])
)

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[SudeepDuvvuru]

Consider it as data quality issue. In power query replace nulls with zeroes.

[praeepmaharathi]

Hi,

I am dealing with null only condition, when actual is null and U/M column value is Managed -Combined then it will populate (sum of managed actual value). Any Dax formula for this to calculate child value and paste that value to null field

Example: Managed - Combined Actual null value should be $26 (Manage value)

               Unmanged -Combined Actual null value should be $3 (unmanaged 2+1)