Forum Discussion
Continued ContributorDivision 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
- littlemojopuppy
Community Champion
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
- 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() ) - Icey
Community Support
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.
