Count Services inside a Promo Combo Sales

[brunofranc]

How can I count diferent sales inside Combo Order? 
There is a table with all services (table_services) and another with combos (table_combos).
In the "transactions_table" use all the codes, but the challenge is report total of services and minutes sales in a bar graphic.

For Examples:
Service 1 - 10 Sales - 300 minutes

Service 2 - 10 Sales - 450 minutes

...

Is possible create a measure to calculate this?

The tables are bellow:

table_services

IDService	Name	Duration (min)
501	Service1	30
502	Service2	45
503	Service3	90
504	Service4	60

table_combos

IDcombos	Description
1005	2 x 502  + 3 x 504
1006	6 x 501

transactions_table

Date	Orderid	Clientid	IDs
20/02/2019	0123	001	1005
20/02/2019	0123	001	504
01/03/2019	0156	002	1006
02/03/2019	0189	003	501

[v-lionel-msft]

Hi @brunofranc ,

How are these two values calculated? I don't quite understand their calculation logic.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[brunofranc]

For example, in order id 123, there are 1 combo 1005 and 1 of 504. 
Combo 1005 is:

2 x IDService 502 and 3 x Service ID504

And then 2 x 45 = 90 min 

               3 x 60 = 180 min
And the amount is 2 +3 = 5
 

[brunofranc]

[v-lionel-msft · Accepted solution]

Hi @brunofranc ,

Please refer to my .pbix file.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@brunofranc , You need to split table_combos twice.

Split Description by + in new rows

And the split the new column into 2 columns on X

You will have your combo split into a row with Qty and service Id

Refer how to do (Edit Query = Data Transformation )

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

[brunofranc]

It's not in this way.
Maybe like this: 
The ID equals a sum  of other 2 IDs.