Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I can't seem to get my measure to correctly calculate both at the row level and at the subtotal level.
For example:
The ABS Delta column is calculating correctly, except for where you see "NaN." I want those values to be the sum of the ABS Delta values that are populated.
I have 3 measures:
SAP Size Curve% =
IF(HASONEVALUE(Dim_Style_Master[Material]),sum(Size_Curve_Upload[Size Curve %]),AVERAGEX(Size_Curve_Upload,Size_Curve_Upload[Size Curve %]))
SO Bookings Curve =CALCULATE(SUM(AllHist_SalesOrders[Adj_SO_QTY]))/CALCULATE(SUM(AllHist_SalesOrders[Adj_SO_QTY]),All(Dim_UPC_Report[Size_Num],Dim_UPC_Report[Width_Dimension]))
ABS_Delta = [SZ_CRV_%] - IF(HASONEVALUE(Dim_UPC_Report[Size_Num]),SUMX(Dim_Style_Master,[SO_Bookings_Curve]),CALCULATE(SUMX(AllHist_SalesOrders,[SO_Bookings_Curve])))
You can also see the data model below to see how the tables link together.
Any suggestions are greatly appreciated!
Thanks
Hi @rjgillin13
Since I can’t see the dataset, it is difficult to analyze.
Please check some modification in “ABS_Delta” measure to see if it works well as you expected
ABS_Delta = [SZ_CRV_%] - IF ( HASONEVALUE ( Dim_Style_Master[Material] ), SUMX ( Dim_Style_Master, [SO_Bookings_Curve] ), CALCULATE ( SUMX ( AllHist_SalesOrders, [SO_Bookings_Curve] ) ) )
Or
ABS_Delta = IF ( HASONEVALUE ( Dim_Style_Master[Material] ), IF ( HASONEVALUE ( Dim_UPC_Report[Size_Num] ), [SZ_CRV_%] - SUMX ( Dim_Style_Master, [SO_Bookings_Curve] ), [SZ_CRV_%] - CALCULATE ( SUMX ( AllHist_SalesOrders, [SO_Bookings_Curve] ) ) ) )
Best regards
Maggie
Hi Maggie,
Thanks for your help. Your first suggestion gets me close, but the subtotals and totals are still not calculating correctly (See below). The Subtotal for "D-Width" and "1012771" should be the sum of the "size-num" row values not the aggregate calculation.
Hi @rjgillin13
Please try this formula
ABS_Delta = IF ( HASONEVALUE ( Dim_Style_Master[Material] ), [SZ_CRV_%] - SUMX ( Dim_Style_Master, [SO_Bookings_Curve] ), [SZ_CRV_%] - CALCULATE ( SUMX ( AllHist_SalesOrders, [SO_Bookings_Curve] ) ) )
Best Regards
Maggie
Hi Maggie,
Thanks for the reply. Unfortanately I am still getting the same result:
ABS_Delta_2 = abs(if(HASONEVALUE(Dim_Style_Master[Material]),[SZ_CRV_%]-SUMX(Dim_Style_Master,[SO_Bookings_Curve]),[SZ_CRV_%]-CALCULATE(SUMX(AllHist_SalesOrders,[SO_Bookings_Curve]))))
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 |
---|---|
104 | |
101 | |
81 | |
79 | |
67 |
User | Count |
---|---|
122 | |
110 | |
94 | |
82 | |
77 |