Forum Discussion
Grouping or bin level sort is not working using switch DAX
Im trying to migrate existing tableau range selection to PBI. Over all total value matches but group by (condtional sorting) is not not working as expected.
I have a transaction table for retail mart. Discount and Gallons are direct measures and Transaction id is row level data with multiple records for same transaction ids.
Step1:
Discount Per Gallon =
{FIXED [Transaction Id]: avg(([Discount]))/avg(([Gallons])) }
Step2:
Discount Per Gallon Range =
IF ([Discount Per Gallon]) >= 0 AND ([Discount Per Gallon]) <= .05 THEN "0.0 - 0.5" ELSEIF
([Discount Per Gallon]) > .05 AND ([Discount Per Gallon]) <= .10 THEN ".5 - .10" ELSEIF
([Discount Per Gallon]) > .10 AND ([Discount Per Gallon]) <= .15 THEN ".10 - .15" ELSEIF
([Discount Per Gallon]) > .15 AND ([Discount Per Gallon]) <= .20 THEN ".15 - .20" ELSEIF
([Discount Per Gallon]) > .20 AND ([Discount Per Gallon]) <= .25 THEN ".20 - .25" ELSEIF
([Discount Per Gallon]) > .25 THEN "> .25" END
Same is achieved in Power bi using below calculations:
Average of Discount Per Gallons = CALCULATE(AVERAGE(RETAIL_MART[Discount Per Gallon]), ALLEXCEPT(RETAIL_MART,RETAIL_MART[TRANSACTION_ID]))
Here, Discount Per Gallon = calcuated column of ( Discount/Gallons) at transform layer.
Groupie =
VAR DiscountPerGallon = [Average of Discount Per Gallons]
RETURN
SWITCH(
TRUE(),
DiscountPerGallon >=0 && DiscountPerGallon <= 0.05 , "0.0 - 0.05",
DiscountPerGallon > 0.05 && DiscountPerGallon <= 0.10 , "0.05 - 0.10",
DiscountPerGallon > 0.10 && DiscountPerGallon <= 0.15 , "0.10 - 0.15",
DiscountPerGallon > 0.15 && DiscountPerGallon <= 0.20 , "0.15 - 0.20",
DiscountPerGallon > 0.20 && DiscountPerGallon <= 0.25 , "0.20 - 0.25",
DiscountPerGallon > 0.25, ">0.25",
"NULL"
)
But the calcualted result looks different . Expected ResultActual Result
2 Replies
- AnonymousNot applicable
Id Discount Gallons or quantity fb06e2a 0 132c2ab 0.65 6.52 a64ed3e 0.61 6.12 d5ade64 0 874be3e 2.3 22.97 fa6790b 0 d3990fa 1.35 13.52 623f4a2 1.8 18.02 81841d0 0.9 8.95 d5e48a9 2.75 27.48 0f72051 0.94 9.36 d98ae7a 0 6607fa0 0 62257a6 1.62 8.08 0c4156c 1.73 17.32 a56e30e 1.37 6.83 000a838 0 9.96E+23 1.46 14.57 1af7f9b 1.2 12.01 1f473aa 1.3 12.96 d98ae7a 3.69 18.47 d5ade64 1.65 8.24 000a838 3.24 16.18 129e8fb 1.63 16.31 386c506 1.61 16.12 d23043d 1.09 10.89 3f1b9cf 0.41 4.05 9e6b5c9 0.95 9.47 6607fa0 4.02 20.12 b19840d 1.69 8.47 92037bc 1.15 11.49 fa6790b 2.19 10.95 7311529 1.43 14.33 62257a6 0 b1db8d4 1.02 10.19 cd90e22 1.32 13.16 8223fea 1.26 12.63 b19840d 0 2e175de 1.34 13.41 a56e30e 0 fb06e2a 1.81 9.05 90e8255 1.01 10.11 d2d7af4 1.66 16.57 Here,
Trans Count = DISTINCTCOUNT('Table',[TRANSACTION_ID])ExpectedActual
- AnonymousNot applicable
Hi Anonymous
According to my judgment, you didn't give the complete sample, so I can't reproduce your formula and slicer, can you give the complete data so that we can help you better? How to provide sample data in the Power BI Forum - Microsoft Fabric Community . Or show it as a screenshot or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.
Best Regards,
Yulia Xu