Help with Sorting

[Koy]

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

[Koy · Accepted solution]

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]

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

[lbendlin]

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

[Koy]

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

[lbendlin]

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

[lbendlin]

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

[Koy]

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]

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

[Koy]

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])

)

[Koy]

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])

)