Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    IdDiscountGallons or quantity
    fb06e2a 0
    132c2ab0.656.52
    a64ed3e0.616.12
    d5ade64 0
    874be3e2.322.97
    fa6790b 0
    d3990fa1.3513.52
    623f4a21.818.02
    81841d00.98.95
    d5e48a92.7527.48
    0f720510.949.36
    d98ae7a 0
    6607fa0 0
    62257a61.628.08
    0c4156c1.7317.32
    a56e30e1.376.83
    000a838 0
    9.96E+231.4614.57
    1af7f9b1.212.01
    1f473aa1.312.96
    d98ae7a3.6918.47
    d5ade641.658.24
    000a8383.2416.18
    129e8fb1.6316.31
    386c5061.6116.12
    d23043d1.0910.89
    3f1b9cf0.414.05
    9e6b5c90.959.47
    6607fa04.0220.12
    b19840d1.698.47
    92037bc1.1511.49
    fa6790b2.1910.95
    73115291.4314.33
    62257a6 0
    b1db8d41.0210.19
    cd90e221.3213.16
    8223fea1.2612.63
    b19840d 0
    2e175de1.3413.41
    a56e30e 0
    fb06e2a1.819.05
    90e82551.0110.11
    d2d7af41.6616.57

    Here,

    Trans Count = DISTINCTCOUNT('Table',[TRANSACTION_ID])

    ExpectedActual

    • Anonymous's avatar
      Anonymous
      Not 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