Process a table with some values to sum and some not to

[tvycho]

Dear all,

I struggle with a table of sales that I need to process, and I am quite new to all this, so I may even describe things incorrectly, sorry for that. I kindly ask you to help me with my issue.

I have a dataset from our box office that sells either a single ticket (the last line 10466714072 in the example below) or a combo ticket (all previous tickets). However, the box stores the data in a strange way: it devides the price of combo tickets to three entries but it copies the number of people in all three.

Here is a sample of the original dataset:

TICKET_ID	NUM_PEOPLE	PRICE
10466713925	4	0
10466713925	4	215
10466713925	4	215
10466713941	2	0
10466713941	2	195
10466713941	2	195
10466714008	2	0
10466714008	2	220
10466714008	2	220
10466714072	3	840

And here is my desired result, where I sum the prices but not the people. For further analysis, I also need to distinguish between a single and a combo ticket, as in the COMBO column (0=single, 1=combo). You can only tell the entry is a single ticket by having a single row, whereas combo tickets are in three lines:

TICKET_ID	NUM_PEOPLE	PRICE	COMBO
10466713925	4	430	1
10466713941	2	390	1
10466714008	2	440	1
10466714072	3	840	0

Could you please suggest a way how to do this? Thank you.

[Anonymous · Accepted solution]

Hi @tvycho ,

Please create a new measure:

COMBO = IF(ISODD(SUM('Table'[NUM_PEOPLE])),0,1)

Output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[tvycho]

Dear Gao,

thank you! Although I finally chose a different solution (a new column with a combination of SUMMERIZE, IF, SUM and COUNT), your solution inspired me a lot and change my perpective (and it did work, I just decided to choose a different path later).

All the best

Tomáš