Forum Discussion

Koy's avatar
Koy
Helper I
1 year ago
Solved

Help with Sorting

Trying to sort a column Line Item by Line Item Sort as shown below.  Getting an error message and I just can't see why ...  I don't see any duplicates

 

Running a similar table and it works...

Appreciate any help!
Koy

Need Help
  • Koy's avatar
    Koy
    1 year ago

    I did a reboot, and now it works as expected.  Didn't change anything, so was glitchin the martrix for a bit I guess.

10 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    Is that first Line Item a BLANK() or an empty string?

    • Koy's avatar
      Koy
      Helper I
      1 year ago

      I am using this table to help format this measure as a P&L view  similar to this which uses the second table I screenshotted that had no issues....

      Gross to Net OL =
       VAR _disc = CALCULATE(SUMX('1_BPC','1_BPC'[FIN_DATA]),'5_AcctRef'[Account Number]="6541303") / [FX]
       
       RETURN
       switch(SELECTEDVALUE('Gross to Net'[Category]),
          "Gross Sales",
              if(HASONEVALUE('Gross to Net'[Line Item]),
                  Switch(SELECTEDVALUE('Gross to Net'[Line Item]),
                      "Discount",format(_disc / [Units] *1000,[format2]),
                      "Buydown",format([BD]/[Units]*1000,[format2])
                  ),format([GS] / [Units]*1000,[format2])
             ),
          "Price, net",
              if(HASONEVALUE('Gross to Net'[Line Item]),
                  Switch(selectedvalue('Gross to Net'[Line Item]),
                      "Key Acct",format([KA]/[Units]*1000,[format2]),
                      "SMP",format([SMP]/[Units]*1000,[format2]),
                      "Coupon",format([Coup]/[Units]*1000,[format2]),
                      "WHS Terms",format(([To Customer] - _disc)/[Units]*1000,[format2]),
                      "Retail Prgm",format([To Store]/[Units]*1000,[format2]),
                      "FET",format([FET]/[Units]*1000,[format2])
                  ),format(([GS]+[BD]+ _disc) / [Units] *1000,[format2])
              ),
          "Net Revenue",format([NR]/[Units]*1000,[format2])
      )
    • Koy's avatar
      Koy
      Helper I
      1 year ago

      Using it to make a table like the below using measures like this 

      Gross to Net OL =
       VAR _disc = CALCULATE(SUMX('1_BPC','1_BPC'[FIN_DATA]),'5_AcctRef'[Account Number]="6541303") / [FX]
       
       RETURN
       switch(SELECTEDVALUE('Gross to Net'[Category]),
          "Gross Sales",
              if(HASONEVALUE('Gross to Net'[Line Item]),
                  Switch(SELECTEDVALUE('Gross to Net'[Line Item]),
                      "Discount",format(_disc / [Units] *1000,[format2]),
                      "Buydown",format([BD]/[Units]*1000,[format2])
                  ),format([GS] / [Units]*1000,[format2])
             ),
          "Price, net",
              if(HASONEVALUE('Gross to Net'[Line Item]),
                  Switch(selectedvalue('Gross to Net'[Line Item]),
                      "Key Acct",format([KA]/[Units]*1000,[format2]),
                      "SMP",format([SMP]/[Units]*1000,[format2]),
                      "Coupon",format([Coup]/[Units]*1000,[format2]),
                      "WHS Terms",format(([To Customer] - _disc)/[Units]*1000,[format2]),
                      "Retail Prgm",format([To Store]/[Units]*1000,[format2]),
                      "FET",format([FET]/[Units]*1000,[format2])
                  ),format(([GS]+[BD]+ _disc) / [Units] *1000,[format2])
              ),
          "Net Revenue",format([NR]/[Units]*1000,[format2])
      )

       

    • lbendlin's avatar
      lbendlin
      Super User
      1 year ago

      No, i mean in the reference/sort table, not in the fact table.

      • Koy's avatar
        Koy
        Helper I
        1 year ago

        It does not.  Sorry I am new to all this, but here is what the output looks like.  My replies keep getting deleted as SPAM for some reason.

        Based on this measure for the first column...

         

         

  • Koy's avatar
    Koy
    Helper I
    1 year ago

    I did a reboot, and now it works as expected.  Didn't change anything, so was glitchin the martrix for a bit I guess.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Thank you lbendlin 

      Hi, Koy 

      Your current sort is working. You can share your DAX expression here and mark it as a solution. This way, so that others in the community can quickly find answers to similar problems, reducing the time they spend searching for answers.
      Your sharing will work with us to promote the development of the community. Thank you again for your cooperation.

       

       

      Best Regards

      Jianpeng Li