Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
Please can someone help me with my calculation.
I have a dataset which looks like the below, I cannot change the structure of the data. I need to create a calculation which will multiply the 'UnitOfMeasure' (Recycled and Reused) against the associated Unit Weight for that 'AssetType'. e.g. Laptops Recycled (1438) * Laptops Unit Weight (2) = 2876
KpiDate | AssetType | UnitOfMeasure | volume |
30/06/2023 | Docks | Recycled | 613 |
30/06/2023 | Docks | Reused | 10,492 |
30/06/2023 | Docks | Unit Weight | 1 |
30/06/2023 | keyboard / mouse | Recycled | 1,219 |
30/06/2023 | keyboard / mouse | Reused | 4,357 |
30/06/2023 | keyboard / mouse | Unit Weight | 1 |
30/06/2023 | Laptops | Recycled | 1,438 |
30/06/2023 | Laptops | Reused | 16,676 |
30/06/2023 | Laptops | Unit Weight | 2 |
30/06/2023 | Mobility | Recycled | 20 |
30/06/2023 | Mobility | Reused | 20 |
30/06/2023 | Mobility | Unit Weight | 1 |
30/06/2023 | Monitors | Recycled | 450 |
30/06/2023 | Monitors | Reused | 9,571 |
30/06/2023 | Monitors | Unit Weight | 8 |
30/06/2023 | Networking | Recycled | 202 |
30/06/2023 | Networking | Reused | 322 |
30/06/2023 | Networking | Unit Weight | 5 |
30/06/2023 | Printers | Recycled | - |
30/06/2023 | Printers | Reused | 11 |
30/06/2023 | Printers | Unit Weight | 20 |
30/06/2023 | Server | Recycled | 25 |
30/06/2023 | Server | Reused | 26 |
30/06/2023 | Server | Unit Weight | 12 |
30/06/2023 | Storage | Recycled | 288 |
30/06/2023 | Storage | Reused | 231 |
30/06/2023 | Storage | Unit Weight | 1 |
30/06/2023 | Systems | Recycled | 778 |
30/06/2023 | Systems | Reused | 6,379 |
30/06/2023 | Systems | Unit Weight | 3 |
I need to calculate the total for Recycled and Reused, in the example provided I should be returned with the total below of 12,218 Recycled and 146,299 Reused. I need to work out the row by row values for each asset type and then be able to role this up to the 'UnitofMeasure' so I can great graphs for 'Recycled' and 'Reused'
Asset Type | KPI Date | UnitOfMeasure(Recycled) | UnitOfMeasure(Reused) |
Docks | 30/06/2023 | 631 | 10,492 |
keyboard / mouse | 30/06/2023 | 1,219 | 4,357 |
Laptops | 30/06/2023 | 2,876 | 33,352 |
Mobility | 30/06/2023 | 20 | 20 |
Monitors | 30/06/2023 | 3,600 | 76,568 |
Networking | 30/06/2023 | 1,010 | 1,610 |
Printers | 30/06/2023 | - | 220 |
Server | 30/06/2023 | 300 | 312 |
Storage | 30/06/2023 | 228 | 231 |
Systems | 30/06/2023 | 2,334 | 19,137 |
Total | Total | 12,218 | 146,299 |
I've tried the below measure which was worked against 'AssetType' but didn't like me rolling up to 'UnitofMeasure'
1.
Solved! Go to Solution.
@Raymz112
You can add an additional measure as follows this will work based on the selected UOM,
UOM = IF( SELECTEDVALUE( Table7[UnitOfMeasure] ) = "Recycled" , [UnitOfMeasure(Recycled)] , [UnitOfMeasure(Reused)] )
You can add this to your chart
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hello @Raymz112 ,
You can use the below measure to get the desired result
1. First Pivot your table such that Recycled, Reused and Unit_Weight are separate columns
2. Create the below 2 measures
Measure4 = SUMX('Table', 'Table'[Recycled] * 'Table'[Unit Weight])
Measure5 = SUMX('Table', 'Table'[Reused] * 'Table'[Unit Weight])
Regards,
Hello,
Thank you for your help on this but I need to keep the data model the same. Do you know of a solution which doesn't involve pivoting?
@Raymz112
Create the following three measures and add it to a matrix viz, make sure you have Asset Type alrady added in your Matrix Row section:
KPI Date = CALCULATE( MAX( Table7[KpiDate] ) )
------------------------------------------------------
UnitOfMeasure(Reused) =
SUMX(
VALUES(Table7[AssetType]),
VAR __Reused = CALCULATE( MAX( Table7[volume] ) , Table7[UnitOfMeasure] = "Reused" )
VAR __UnitWeigh = CALCULATE( MAX( Table7[volume] ) , Table7[UnitOfMeasure] = "Unit Weight" )
VAR __Result = __Reused * __UnitWeigh
RETURN
__Result
)
-------------------------------------------------------
UnitOfMeasure(Recycled) =
SUMX(
VALUES(Table7[AssetType]),
VAR __Recycled = CALCULATE( MAX( Table7[volume] ) , Table7[UnitOfMeasure] = "Recycled" )
VAR __UnitWeigh = CALCULATE( MAX( Table7[volume] ) , Table7[UnitOfMeasure] = "Unit Weight" )
VAR __Result = __Recycled * __UnitWeigh
RETURN
__Result
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hello,
Thank you for your help, unfortunately it doesn't work as the totals at the bottom are incorrect. I also want the associated value to show next to the 'UnitofMeasure' so that I can use it in my visuals, displaying the difference between the two.
The below is what I got from your calculation
@Raymz112
Please check the reply, I just updated it
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
The update works as shown below but then doesn't allow me to filter the data from the 'UnitofMeasure' column which I need for my visualisations. I ideally want all of them to be in one measure and filterable by 'UnitofMeasure' being either 'Recycle' or 'Reused'
@Raymz112
You can add an additional measure as follows this will work based on the selected UOM,
UOM = IF( SELECTEDVALUE( Table7[UnitOfMeasure] ) = "Recycled" , [UnitOfMeasure(Recycled)] , [UnitOfMeasure(Reused)] )
You can add this to your chart
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Smashed it - thank you! 🙂
User | Count |
---|---|
96 | |
85 | |
77 | |
66 | |
63 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |