Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I have two main tables. The first is the [ItemLedger]. It lists the users, their transaction and the date it occurred.
USERID | TransactionType | DATE |
A01 | ORD.PICK | 4/13/2021 |
A02 | ORD.PICK | 4/13/2021 |
A03 | ORD.PICK | 4/13/2021 |
A01 | ORD.SHIP | 4/13/2021 |
A02 | ORD.SHIP | 4/13/2021 |
A03 | ORD.SHIP | 4/13/2021 |
A01 | ITEM.PUTAWAT | 4/13/2021 |
A02 | ORD.SHIP | 4/13/2021 |
A03 | ORD.SHIP | 4/13/2021 |
My second table is my weight table, [Weights].
USERID | TransactionType | Weight |
A01 | ORD.PICK | 1 |
A01 | ORD.SHIP | 3 |
A01 | ITEM.PUTAWAT | 5 |
A02 | ORD.PICK | 2 |
A02 | ORD.SHIP | 4 |
A02 | ITEM.PUTAWAT | 6 |
A03 | ORD.PICK | 1 |
A03 | ORD.SHIP | 1 |
A03 | ITEM.PUTAWAT | 10 |
Example Intermidate Tables:
ORD.PICK | ORD.SHIP | ITEM.PUTAWAT | |
A01 | 1 | 1 | 1 |
A02 | 1 | 2 | 0 |
A03 | 1 | 2 | 0 |
Transaction Count = Countrows(ItemLedger)
ORD.PICK | ORD.SHIP | ITEM.PUTAWAT | |
A01 | 1 | 3 | 5 |
A02 | 2 | 4 | 6 |
A03 | 1 | 1 | 10 |
ORD.PICK | ORD.SHIP | ITEM.PUTAWAT | |
A01 | 1 | 0.33 | 0.2 |
A02 | 0.5 | 0.5 | 0 |
A03 | 1 | 2 | 0 |
TransactionCount / Hourly Target
Notes:
From my screenshot below, the output table is showing correctly the individal transaction types but the totals are simply the total transaction count dividided by the sum of the weight. For ASEGOVIA, 66 transactions / 80 hours yeilding 0.83. The true output should be 6.03 or (1/35) + (58/10) + (7/35).
Lastly, there is also a diminsional table with all userIDs and other dim table with all transaction types to build the nesscary relationships.
Solved! Go to Solution.
Productivity Hours =
SUMX(ItemLedger, DIVIDE([Transaction Count], [Hourly Target]))
Hi @EnrichedUser,
If I understand correctly, your issue is that the matrix is not showing the totals you expected.
You can use HASONEVALUE to define the logic for calculating the totals.
Just like:
IF(HASONEVALUE([USERID]),
measure1, // Original MEASURE
measure2, // Total calculation logic
)
Best Regards,
Link
Productivity Hours =
SUMX(ItemLedger, DIVIDE([Transaction Count], [Hourly Target]))
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |