Home Forums Power Pivot Does this require CONTAINS?

This topic contains 1 reply, has 2 voices, and was last updated by  brawnystaff 2 months ago.

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #14378

    Bel
    Participant
    • Started: 4
    • Replies: 0
    • Total: 4

    Hi

    I have a scenario:

    Company                                        Amount

    Apple                                               5

    Orange                                            10

    Banana                                           15

    Apple, Orange, Banana              20

    Orange, Banana                           25

    I want to count sales by individual “Fruit” i.e. Apple on its own 2 and the SUM to display 25

    How can I achieve this? Any ideas will be much appreciated!

    Cheers

    Bel

     

    #15239

    brawnystaff
    Participant
    • Started: 6
    • Replies: 20
    • Total: 26

    Not sure if you have this solved, but regarding your issue, probably would load the data into Power Query (a.k.a Excel Get and Transform) first to create a unique company list (UnPivot by Comma Delimiter to Rows, Trim, then Remove Duplicates), load to Data Model, and then use Power Pivot to create two measures: one to count and one to sum, as specified.

    See attached sample spreadsheet.  Rather than use the “Contains” operator, I used the “SEARCH” operator in PowerPivot.

    Attachments:
    You must be logged in to view attached files.
Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.