Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |