Forum Discussion

Koy's avatar
Koy
Icon for Helper I rankHelper I
2 years 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 wor...
Need Help
  • Koy's avatar
    Koy
    2 years 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.

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

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

  • Koy's avatar
    Koy
    Icon for Helper I rankHelper I
    2 years 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
    Icon for Helper I rankHelper I
    2 years 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
    Icon for Super User rankSuper User
    2 years ago

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

    • Koy's avatar
      Koy
      Icon for Helper I rankHelper I
      2 years 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...

       

       

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        2 years ago

        You seem to be mixing topics. Your original question was about the sorting issue?

  • Koy's avatar
    Koy
    Icon for Helper I rankHelper I
    2 years ago

    Sorry, misunderstood.  The first line item is just an empty cell.  

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      2 years ago

      Try without it. I don't think you can sort by BLANK()