Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I work in a company that lends equipment and machinery for construction projects.
I have +100 equipment, each identify by an unique ID.
I have a log of all the borrowed equipment.(Status: "Active", id 1)
And a log of all the equipment being repaired.(Status: "Broken", id 2)
I dont't have a daily log of all the equipment available, waiting to be borrow (Status: "Standby", id 0)
I've combine both log tables, resulting:
Date ID.Equip Status
10/5/2020 A101 Active
10/5/2020 A102 Active
10/5/2020 A103 Broken
.
.
.
10/6/2020 A101 Active
10/6/2020 A102 Broken
10/6/2020 A103 Active
.
.
.
10/8/2020 A101 Active
10/10/2020 A102 Broken
11/11/2020 B103 Active
If an equiment isn't shown in this table, it means it's in "Standby" for that date (the date is not shown either, as it jumps to the next registry). And not all the equipment ids are listed, because not all equipment have had being "active" or "broken" atm.
I also have a database table with a list of all the equipment.
A101
A102
A103
A104
B101
B102
.
.
.
And a calendar table.
I want to create a visual that shows the % of the days "Broken" in a month from the total of days
And a visual that shows the % of the "Active" days from the "Standby" days
And maybe a combination of "Standby", "Active" and "Broken" as a stacked or 100% stacked chart.
I've attached and Example of the data and the types of visuals I have in mind:
PowerBI
Solved! Go to Solution.
@iandespradel , Try like
% of the days Broken =
var _tot = countx(equipment,equipment[ID])
return
divide(calculate(distinctcount(Table[ID.Equip]), filter(Table, Table[Status] ="Broken")), _tot)
% of the days Active=
var _tot = countx(equipment,equipment[ID])
return
divide(calculate(distinctcount(Table[ID.Equip]), filter(Table, Table[Status] ="Active")), _tot)
Hi @iandespradel ,
Based on your description, you can do some steps as follows.
NEW = CROSSJOIN(ALLSELECTED('00_CONTROL'[Fecha]),ALLSELECTED(iBD_Equipos[Ficha]))
Status =
var x1=MAXX(FILTER(ALL('00_CONTROL'),'00_CONTROL'[Fecha]=EARLIER('NEW'[Fecha])&&'00_CONTROL'[Ficha]=EARLIER(NEW[Ficha])),[ID.StatusEq])
return
IF(x1<>BLANK(),x1,0)
Ca_Status = SWITCH([Status],1,"Active",2,"Broken",0,"Standby")
_total = CALCULATE(COUNT('NEW'[Ficha]),ALLEXCEPT(NEW,NEW[Fecha],NEW[Ca_Status],'NEW'[Fecha].[Month]))
Result:
Hope that's what you were looking for.
Best Regards,
Yuna
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@iandespradel , Try like
% of the days Broken =
var _tot = countx(equipment,equipment[ID])
return
divide(calculate(distinctcount(Table[ID.Equip]), filter(Table, Table[Status] ="Broken")), _tot)
% of the days Active=
var _tot = countx(equipment,equipment[ID])
return
divide(calculate(distinctcount(Table[ID.Equip]), filter(Table, Table[Status] ="Active")), _tot)
Hey,
Thanks for the reply.
The thing is that the table doesn't have all the calendar days. Just the days the equipment were on site. The rest of the days they were on Standby, and those dates are not in the table.
But you give me an idea for a possible solution. Let me check and do a test run.
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |