Forum Discussion
Division based on condition
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
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.
6 Replies
This doesn't work?
DIVIDE( SUM([Area]), SUM([Headcount]), BLANK() )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?
Create two variables, in each check that numerator and demonimator are not blank. Then check that both are true, and if so divide
VAR HeadcountAvailable = ISBLANK(SUM(Headcount)) VAR AreAvailable = ISBLANK(SUM(Area)) RETURN IF( AND( HeadCountAvailable = FALSE(), AreaAvailable = FALSE() ), DIVIDE( SUM([Area]), SUM([Headcount]), BLANK() ), BLANK() )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.
