Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
Below is a screenshot from my report.
I have an extract from SAP. In the report it gives me all the possible billing quantities. I need to find the right quantities, based on the Sales Unit. In the formula I set up the relationships between the sales unit and the quantity. However, PBI tells me that the formula is too difficult. I couldn't get it to work with Switch either. How can I get the correct billed quantities in 1 single column?
Solved! Go to Solution.
try to split my measure into 4 type measures
Invoiced_QTY1, Invoiced_QTY2, Invoiced_QTY3, Invoiced_QTY4
Invoiced_QTY1 =
VAR _SalesUnit = [SalesUnit]
VAR _t1 = _SalesUnit="KG"
VAR _t2 = _SalesUnit in {"LB", "/LB"}
VAR _t3 = _SalesUnit ="BX"
VAR St_UOM = SWITCH(TRUE(),
_t1,[InvQtyKG],
_t2 , [InvQtyE],
_t3,[InvQtyBX],
_
RETURN
St_UOM
----------------
Invoiced_QTY2 =
VAR _SalesUnit = [SalesUnit]
VAR _t4 = _SalesUnit ="CT"
VAR _t5 = _SalesUnit ="SM"
VAR _t6 = _SalesUnit ="LT"
VAR St_UOM = SWITCH(TRUE(),
_t4,[InvQtyCT],
_t5,[InvQtySM],
_t6,[InvQtyLTR],
RETURN
St_UOM
-----------------
Invoiced_QTY3 =
VAR _SalesUnit = [SalesUnit]
VAR _t7 = _SalesUnit ="PF"
VAR _t8 = _SalesUnit ="RL"
VAR _t9 = _SalesUnit in {"DR", "KT","EA"}
VAR REVENUE = [InvGrossAnrtDocCurr]
VAR St_UOM = SWITCH(TRUE(),
_t7,[InvQtyPF],
_t8,[InvQtyRL],
_t9,[InvQtyEA],
RETURN
St_UOM
------------------
Invoiced_QTY4 =
VAR _SalesUnit = [SalesUnit]
VAR _t10 = _SalesUnit in {"GR", "OC","LF","BG","SF","SY","SH","FT","YD","MR"}
VAR St_UOM = SWITCH(TRUE(),
_t10,[InvQtyS])
RETURN
St_UOM
-------------------------
Invoiced_QTY_Final =
VAR REVENUE = [InvGrossAnrtDocCurr]
RETURN
DIVIDE(REVENUE,[Invoiced_QTY1]+[Invoiced_QTY2]+[Invoiced_QTY3]+[Invoiced_QTY4],0)
pls try this
Invoiced_QTY =
VAR _SalesUnit = [SalesUnit]
VAR _t1 = _SalesUnit="KG"
VAR _t2 = _SalesUnit in {"LB", "/LB"}
VAR _t3 = _SalesUnit ="BX"
VAR _t4 = _SalesUnit ="CT"
VAR _t5 = _SalesUnit ="SM"
VAR _t6 = _SalesUnit ="LT"
VAR _t7 = _SalesUnit ="PF"
VAR _t8 = _SalesUnit ="RL"
VAR _t9 = _SalesUnit in {"DR", "KT","EA"}
VAR _t10 = _SalesUnit in {"GR", "OC","LF","BG","SF","SY","SH","FT","YD","MR"}
VAR REVENUE = [InvGrossAnrtDocCurr]
VAR St_UOM = SWITCH(TRUE(),
_t1,[InvQtyKG],
_t2 , [InvQtyE],
_t3,[InvQtyBX],
_t4,[InvQtyCT],
_t5,[InvQtySM],
_t6,[InvQtyLTR],
_t7,[InvQtyPF],
_t8,[InvQtyRL],
_t9,[InvQtyEA],
_t10,[InvQtyS])
RETURN
DIVIDE(REVENUE,St_UOM,0)
Thank you,
Just tried it and I get the same error message:
OLE or ODBC error: We couldn't fold the expression to the data source. Please try a simpler expression..
try to split my measure into 4 type measures
Invoiced_QTY1, Invoiced_QTY2, Invoiced_QTY3, Invoiced_QTY4
Invoiced_QTY1 =
VAR _SalesUnit = [SalesUnit]
VAR _t1 = _SalesUnit="KG"
VAR _t2 = _SalesUnit in {"LB", "/LB"}
VAR _t3 = _SalesUnit ="BX"
VAR St_UOM = SWITCH(TRUE(),
_t1,[InvQtyKG],
_t2 , [InvQtyE],
_t3,[InvQtyBX],
_
RETURN
St_UOM
----------------
Invoiced_QTY2 =
VAR _SalesUnit = [SalesUnit]
VAR _t4 = _SalesUnit ="CT"
VAR _t5 = _SalesUnit ="SM"
VAR _t6 = _SalesUnit ="LT"
VAR St_UOM = SWITCH(TRUE(),
_t4,[InvQtyCT],
_t5,[InvQtySM],
_t6,[InvQtyLTR],
RETURN
St_UOM
-----------------
Invoiced_QTY3 =
VAR _SalesUnit = [SalesUnit]
VAR _t7 = _SalesUnit ="PF"
VAR _t8 = _SalesUnit ="RL"
VAR _t9 = _SalesUnit in {"DR", "KT","EA"}
VAR REVENUE = [InvGrossAnrtDocCurr]
VAR St_UOM = SWITCH(TRUE(),
_t7,[InvQtyPF],
_t8,[InvQtyRL],
_t9,[InvQtyEA],
RETURN
St_UOM
------------------
Invoiced_QTY4 =
VAR _SalesUnit = [SalesUnit]
VAR _t10 = _SalesUnit in {"GR", "OC","LF","BG","SF","SY","SH","FT","YD","MR"}
VAR St_UOM = SWITCH(TRUE(),
_t10,[InvQtyS])
RETURN
St_UOM
-------------------------
Invoiced_QTY_Final =
VAR REVENUE = [InvGrossAnrtDocCurr]
RETURN
DIVIDE(REVENUE,[Invoiced_QTY1]+[Invoiced_QTY2]+[Invoiced_QTY3]+[Invoiced_QTY4],0)
I can't share a file. Below is a sample set of the data
BillingDoc | BillingItem | PostingDate | PricingDate | InvGrossAmtDocCurr | InvNetValDocCurr | InvQtyTB | InvQtySM | InvQtyS | InvQtyRL | InvQtyPF | InvQtyLTR | InvQtyKG | InvQtyKD | InvQtyGA | InvQtyEA | InvQtyE | InvQtyCT | InvQtyBX | InvQtyB | SalesUnit | SalesUnit_txt |
7.71E+09 | 10 | ######## | ######## | 1715.2 | 1715.2 | 0 | 22.514 | 10 | 0 | 0.062 | 0 | 24.93 | 0 | 0 | 0 | 54.912 | 0 | 0.062 | 24.93 | SH | Sheet |
7.61E+09 | 10 | ######## | ######## | 6141.61 | 6455.23 | 0 | 13.75 | 25 | 0 | 0 | 0 | 23.525 | 0 | 0 | 25 | 25 | 0 | 25 | 25 | EA | Each |
7.73E+09 | 10 | ######## | ######## | 39933.87 | 39933.87 | 0 | 5305.5 | 50 | 50 | 0 | 0 | 366.08 | 0 | 0 | 0 | 5305.5 | 0 | 0 | 5305.5 | RL | Roll |
7.72E+09 | 10 | ######## | ######## | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | KG | Kilogram |
7.61E+09 | 10 | ######## | ######## | 229720 | 229720 | 0 | 0 | 2809 | 0 | 0 | 0 | 11162.97 | 0 | 2809 | 0 | 24588.03 | 0 | 0 | 11162.97 | GA | Gallon, US |
7.61E+09 | 10 | ######## | ######## | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | BX | Box |
7.62E+09 | 40 | ######## | ######## | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | /LB | per pound |
7.05E+09 | 10 | ######## | ######## | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | YD | Yard |
7.05E+09 | 10 | ######## | ######## | 840 | 840 | 0 | 0 | 100 | 0 | 0.018 | 0 | 9.978 | 0 | 0 | 2 | 2 | 2 | 2 | 2 | UN | Unit |
7.6E+09 | 10 | ######## | ######## | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | SM | Sq Meter |
7.61E+09 | 10 | ######## | ######## | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | LT | Liter |
7.61E+09 | 10 | ######## | ######## | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | GR | Gram |
7.62E+09 | 10 | ######## | ######## | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | DR | Drum |
7.62E+09 | 10 | ######## | ######## | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | LB | Pound |
7.62E+09 | 90 | ######## | ######## | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | CT | Carton |
7.62E+09 | 10 | ######## | ######## | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | FT | Foot |
7.62E+09 | 10 | ######## | ######## | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | PF | Pallet |
7.62E+09 | 10 | ######## | ######## | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | SY | Sq yard |
7.73E+09 | 20 | ######## | ######## | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | LM | Linear m |
7.78E+09 | 10 | ######## | ######## | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | KT | Kit |
7.78E+09 | 10 | ######## | ######## | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | BG | Bag |
BillingDoc | BillingItem | PostingDate | PricingDate | InvGrossAmtDocCurr | InvNetValDocCurr | InvQtyTB | InvQtySM | InvQtyS | InvQtyRL | InvQtyPF | InvQtyLTR | InvQtyKG | InvQtyKD | InvQtyGA | InvQtyEA | InvQtyE | InvQtyCT | InvQtyBX | InvQtyB | SalesUnit | SalesUnit_txt |
7.71E+09 | 10 | 1/16/2023 | 9/16/2022 | 1715.2 | 1715.2 | 0 | 22.514 | 10 | 0 | 0.062 | 0 | 24.93 | 0 | 0 | 0 | 54.912 | 0 | 0.062 | 24.93 | SH | Sheet |
7.61E+09 | 10 | 1/16/2023 | 12/9/2022 | 6141.61 | 6455.23 | 0 | 13.75 | 25 | 0 | 0 | 0 | 23.525 | 0 | 0 | 25 | 25 | 0 | 25 | 25 | EA | Each |
7.73E+09 | 10 | 1/16/2023 | 12/26/2022 | 39933.87 | 39933.87 | 0 | 5305.5 | 50 | 50 | 0 | 0 | 366.08 | 0 | 0 | 0 | 5305.5 | 0 | 0 | 5305.5 | RL | Roll |
7.72E+09 | 10 | 1/16/2023 | 1/13/2023 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | KG | Kilogram |
7.61E+09 | 10 | 1/15/2023 | 1/15/2023 | 229720 | 229720 | 0 | 0 | 2809 | 0 | 0 | 0 | 11162.97 | 0 | 2809 | 0 | 24588.03 | 0 | 0 | 11162.97 | GA | Gallon, US |
7.61E+09 | 10 | 1/15/2023 | 1/15/2023 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | BX | Box |
7.62E+09 | 40 | 1/15/2023 | 1/15/2023 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | /LB | per pound |
7.05E+09 | 10 | 1/16/2023 | 1/16/2023 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | YD | Yard |
7.05E+09 | 10 | 1/16/2023 | 1/16/2023 | 840 | 840 | 0 | 0 | 100 | 0 | 0.018 | 0 | 9.978 | 0 | 0 | 2 | 2 | 2 | 2 | 2 | UN | Unit |
7.6E+09 | 10 | 1/16/2023 | 1/16/2023 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | SM | Sq Meter |
7.61E+09 | 10 | 1/16/2023 | 1/16/2023 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | LT | Liter |
7.61E+09 | 10 | 1/16/2023 | 1/16/2023 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | GR | Gram |
7.62E+09 | 10 | 1/16/2023 | 1/16/2023 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | DR | Drum |
7.62E+09 | 10 | 1/16/2023 | 1/16/2023 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | LB | Pound |
7.62E+09 | 90 | 1/16/2023 | 1/16/2023 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | CT | Carton |
7.62E+09 | 10 | 1/16/2023 | 1/16/2023 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | FT | Foot |
7.62E+09 | 10 | 1/16/2023 | 1/16/2023 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | PF | Pallet |
7.62E+09 | 10 | 1/16/2023 | 1/16/2023 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | SY | Sq yard |
7.73E+09 | 20 | 1/16/2023 | 1/16/2023 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | LM | Linear m |
7.78E+09 | 10 | 1/16/2023 | 1/16/2023 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | KT | Kit |
7.78E+09 | 10 | 1/16/2023 | 1/16/2023 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | BG | Bag |
Hi @Robert1981
Please create a sample PBIX with similar data but data source should be Excel or insert data in Power Queyr. and share here the Google Drive link (after removing all sensitive data).
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |