Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
EnrichedUser
Helper III
Helper III

Divide Measure by Static Value in Another Table - Weighted Total

Hi All,

 

 

I have two main tables. The first is the [ItemLedger]. It lists the users, their transaction and the date it occurred. 

USERIDTransactionTypeDATE
A01ORD.PICK4/13/2021
A02ORD.PICK4/13/2021
A03ORD.PICK4/13/2021
A01ORD.SHIP4/13/2021
A02ORD.SHIP4/13/2021
A03ORD.SHIP4/13/2021
A01ITEM.PUTAWAT4/13/2021
A02ORD.SHIP4/13/2021
A03ORD.SHIP4/13/2021

 

My second table is my weight table, [Weights].

USERIDTransactionTypeWeight
A01ORD.PICK1
A01ORD.SHIP3
A01ITEM.PUTAWAT5
A02ORD.PICK2
A02ORD.SHIP4
A02ITEM.PUTAWAT6
A03ORD.PICK1
A03ORD.SHIP1
A03ITEM.PUTAWAT10

 

Example Intermidate Tables: 

 ORD.PICKORD.SHIPITEM.PUTAWAT
A01111
A02120
A03120

Transaction Count = Countrows(ItemLedger)

 

 

 ORD.PICKORD.SHIPITEM.PUTAWAT
A01135
A02246
A031110
Hourly Target =
CALCULATE(SUM( 'Weights'[Weight]), FILTER('Weights', NOT(ISBLANK([Transaction Count]))))
 
Expected Output
 ORD.PICKORD.SHIPITEM.PUTAWAT
A0110.330.2
A020.50.50
A03120

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).

EnrichedUser_0-1618414688189.png

 

Lastly, there is also a diminsional table with all userIDs and other dim table with all transaction types to build the nesscary relationships. 

1 ACCEPTED SOLUTION
EnrichedUser
Helper III
Helper III

Productivity Hours = 
    SUMX(ItemLedger, DIVIDE([Transaction Count], [Hourly Target]))

View solution in original post

2 REPLIES 2
v-xulin-mstf
Community Support
Community Support

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

EnrichedUser
Helper III
Helper III

Productivity Hours = 
    SUMX(ItemLedger, DIVIDE([Transaction Count], [Hourly Target]))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.