Division based on condition

[simrantuli]

Hi All,

I have to calculate Area per Headcount in my Report for sites and buildings. However, I have area for some sites and headcount also for only some sites.

I have 2 tables. One is People Report and the other table is Area Report.

People Report:

#Key is a custom column which I created by merging Site code and Building.

Area Report:

There's a third table too (Dimension Table) which contains only 1 column i.e. #Key (merge of Site & Building)

My data model looks like below.

I am using the below dax to calculate Area per Person.

And my table in the front end looks like below.

It's good so far. However, I want to show Area per Person in a Card basis Area & Headcount of those sites and buildings for which both are available i.e. Area per Person basis AA##1, AA##2, AB##1, AC##2 in the above image.

Please help!

Link to PBIX: https://drive.google.com/file/d/1EmwvQg0NAh6UCgPE9CBgMYpE14Pc32Bx/view?usp=sharing

[Icey · Accepted solution]

Hi @simrantuli ,

Please check if this is what you want:

Best regards

Icey

If this post helps, then consider Accepting it as the solution to help other members find it faster.

[littlemojopuppy]

VAR	HeadcountAvailable = ISBLANK(SUM(Headcount))
VAR	AreAvailable = ISBLANK(SUM(Area))
RETURN

IF(
	AND(
		HeadCountAvailable = FALSE(),
		AreaAvailable = FALSE()
	),
	DIVIDE(
		SUM([Area]),
		SUM([Headcount]),
		BLANK()
	),
	BLANK()
)

[littlemojopuppy]

This doesn't work?

DIVIDE(
	SUM([Area]),
	SUM([Headcount]),
	BLANK()
)

[simrantuli]

Hi @littlemojopuppy ,

I want to divide them only if both area & headcount are available in that particular site & building combination. Any thoughts on how we can achieve it?

[littlemojopuppy]

Create two variables, in each check that numerator and demonimator are not blank.  Then check that both are true, and if so divide

[simrantuli]

I am sorry if I am asking for too much but can you please write the dax for me. I am very new to Dax. It would be a great help to me. Thanks.