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.
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 |
---|---|
112 | |
97 | |
82 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |