How can i multiple columns from Different tables With Condition other Cols

[lboldrino]

TicketCosts

from   to          year     cost

250	350	2019	166,05
351	450	2019	158,51
451	550	2019	150,96
551	650	2019	143,41
650	100000	2019	135,86
250	350	2020	168,54
351	450	2020	160,89
451	550	2020	153,22
551	650	2020	145,56
650	100000	2020	137,9

tblTotalticket

Closed Month	Year	Total Tickets
2020.03	2020	288
2020.04	2020	286
2020.05	2020	227
2020.08	2020	201
2020.06	2020	205
2020.01	2020	403
2020.07	2020	260
2020.02	2020	260
2019.04	2019	350
2019.06	2019	234
2019.07	2019	220
2019.08	2019	251
2019.11	2019	429
2019.01	2019	401
2019.05	2019	270
2019.02	2019	241
2019.10	2019	295
2019.12	2019	374
2019.09	2019	203
2019.03	2019	264

i need the cost per month where totalTickets beween from /to

2019.01 : if year = 2019 and total tickets between 250 and 350 then Totalcost = 401 *166,05 

tblTotalticket is a summerize Table !!!!! (

Customer_TicketPreis =

SUMMARIZE ( customer_tmp, [Closed Month],customer_tmp[Year], "Total Tickets", SUM(customer_tmp[TicketsAnzahl]))

)

ThanX for Your Ideas! 🙂

[Icey · Accepted solution]

Hi @lboldrino ,

Please check if this is what you want:

Column =
VAR Cost_ =
    CALCULATE (
        SUM ( TicketCosts[cost] ),
        FILTER (
            TicketCosts,
            TicketCosts[year] = tblTotalticket[Year]
                && TicketCosts[from] <= tblTotalticket[Total Tickets]
                && TicketCosts[to] >= tblTotalticket[Total Tickets]
        )
    )
RETURN
    [Total Tickets] * Cost_

Best Regards,

Icey

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

[Pragati11]

Hi @lboldrino ,

You will need to have a relatioship between these 2 tables on a common column.

Have you got any relationship?

Thanks,

Pragati

[lboldrino]

i create a Floag col in Ticketcosts:

250	350	2019	166,05 €	1
351	450	2019	158,51 €	2
451	550	2019	150,96 €	3
551	650	2019	143,41 €	4
650	100000	2019	135,86 €	5
250	350	2020	168,54 €	6
351	450	2020	160,89 €	7
451	550	2020	153,22 €	8
551	650	2020	145,56 €	9
650	100000	2020	137,90 €	10

and in customer_totaltickets: Flag =
switch(
true(),
customer_TicketPreis[Total Tickets] >249 && customer_TicketPreis[Total Tickets] < 351 && customer_TicketPreis[Year]= 2019,1
,customer_TicketPreis[Total Tickets] >350 && customer_TicketPreis[Total Tickets] < 451 && customer_TicketPreis[Year]= 2019,2
,customer_TicketPreis[Total Tickets] >450 && customer_TicketPreis[Total Tickets] < 551 && customer_TicketPreis[Year]= 2019,3
,customer_TicketPreis[Total Tickets] >550 && customer_TicketPreis[Total Tickets] < 651 && customer_TicketPreis[Year]= 2019,4
,customer_TicketPreis[Total Tickets] >650 && customer_TicketPreis[Total Tickets] < 100000 && customer_TicketPreis[Year]= 2019,5
,customer_TicketPreis[Total Tickets] >350 && customer_TicketPreis[Total Tickets] < 451 && customer_TicketPreis[Year]= 2020,7
,customer_TicketPreis[Total Tickets] >450 && customer_TicketPreis[Total Tickets] < 551 && customer_TicketPreis[Year]= 2020,8
,customer_TicketPreis[Total Tickets] >550 && customer_TicketPreis[Total Tickets] < 651 && customer_TicketPreis[Year]= 2020,9
,customer_TicketPreis[Total Tickets] >650 && customer_TicketPreis[Total Tickets] < 100000 && customer_TicketPreis[Year]= 2020,10
,customer_TicketPreis[Total Tickets] >249 && customer_TicketPreis[Total Tickets] < 351 && customer_TicketPreis[Year]= 2020,6
,99)

Closed MonthYearTotal TicketsFlag

2020.03	2020	288	6
2020.04	2020	286	6
2020.05	2020	227	99
2020.08	2020	201	99
2020.06	2020	205	99
2020.01	2020	403	7
2020.07	2020	260	6
2020.02	2020	260	6
2019.04	2019	350	1
2019.06	2019	234	99
2019.07	2019	220	99
2019.08	2019	251	1
2019.11	2019	429	2
2019.01	2019	401	2
2019.05	2019	270	1
2019.02	2019	241	99
2019.10	2019	295	1
2019.12	2019	374	2
2019.09	2019	203	99
2019.03	2019	264	1

This works, do you have a better idea for

customer_totaltickets: Flag =
switch(
true(),
customer_TicketPreis[Total Tickets] >249 && customer_TicketPreis[Total Tickets] < 351 && customer_TicketPreis[Year]= 2019,1
,customer_TicketPreis[Total Tickets] >350 && customer_TicketPreis[Total Tickets] < 451 && customer_TicketPreis[Year]= 2019,2
,customer_TicketPreis[Total Tickets] >450 && customer_TicketPreis[Total Tickets] < 551 && customer_TicketPreis[Year]= 2019,3
,customer_TicketPreis[Total Tickets] >550 && customer_TicketPreis[Total Tickets] < 651 && customer_TicketPreis[Year]= 2019,4
,customer_TicketPreis[Total Tickets] >650 && customer_TicketPreis[Total Tickets] < 100000 && customer_TicketPreis[Year]= 2019,5
,customer_TicketPreis[Total Tickets] >350 && customer_TicketPreis[Total Tickets] < 451 && customer_TicketPreis[Year]= 2020,7
,customer_TicketPreis[Total Tickets] >450 && customer_TicketPreis[Total Tickets] < 551 && customer_TicketPreis[Year]= 2020,8
,customer_TicketPreis[Total Tickets] >550 && customer_TicketPreis[Total Tickets] < 651 && customer_TicketPreis[Year]= 2020,9
,customer_TicketPreis[Total Tickets] >650 && customer_TicketPreis[Total Tickets] < 100000 && customer_TicketPreis[Year]= 2020,10
,customer_TicketPreis[Total Tickets] >249 && customer_TicketPreis[Total Tickets] < 351 && customer_TicketPreis[Year]= 2020,6
,99)