selective sum

[Stevo164]

Hello everybody,

i have the following table SALES that shows the sales per department

.

COD_STORE	DES_STORE	COD_DEPARTMENT	DES_DEPARTMENT	SALES	DATE
0002	STORE_1	0002051	DEPARTMENT_1	$ 1.068.811	20180102
0002	STORE_1	0002052	DEPARTMENT_2	$      77.611	20180102
0002	STORE_1	0002053	DEPARTMENT_3	$    405.608	20180102
0002	STORE_1	0002060	DEPARTMENT_4	$      84.680	20180102
0002	STORE_1	0002061	DEPARTMENT_5	$    222.855	20180102
0002	STORE_1	0002062	DEPARTMENT_6	$    207.413	20180102
0002	STORE_1	0002064	DEPARTMENT_7	$      81.772	20180102
0002	STORE_1	0002066	DEPARTMENT_8	$    280.714	20180102
0002	STORE_1	0002070	DEPARTMENT_9	$      23.850	20180102
0002	STORE_1	0002071	DEPARTMENT_10	$      12.693	20180102
0002	STORE_1	0002072	DEPARTMENT_11	$         3.252	20180102
0002	STORE_1	0002073	DEPARTMENT_12	$         9.124	20180102
0002	STORE_1	0002080	DEPARTMENT_13	$ 2.495.266	20180102
0002	STORE_1	0002081	DEPARTMENT_14	$ 2.495.266	20180102
0002	STORE_1	0002082	DEPARTMENT_15	$ 2.495.266	20180102
0002	STORE_1	0002083	DEPARTMENT_16	$ 2.495.266	20180102
0002	STORE_1	0002084	DEPARTMENT_17	$ 2.495.266	20180102
0002	STORE_1	0002085	DEPARTMENT_18	$ 2.495.266	20180102
0002	STORE_1	0002086	DEPARTMENT_19	$ 2.495.266	20180102
0002	STORE_1	0002099	DEPARTMENT_20	$ 2.495.266	20180102

I've marked in red the departments wich has assigned the sales of the entire store. 

I also have the following table tha defines every department as a part of a segment

COD_STORE	DES_STORE	COD_SEGMENT	DES_SEGMENT	COD_DEPARTMENT	DES_DEPARTMENT
0002	STORE_1	0002A23066	SEGMENT_1	0002060	DEPARTMENT_4
0002	STORE_1	0002A23066	SEGMENT_1	0002066	DEPARTMENT_8
0002	STORE_1	0002A24062	SEGMENT_2	0002061	DEPARTMENT_5
0002	STORE_1	0002A24062	SEGMENT_2	0002062	DEPARTMENT_6
0002	STORE_1	0002A24062	SEGMENT_2	0002064	DEPARTMENT_7
0002	STORE_1	0002A27051	SEGMENT_3	0002051	DEPARTMENT_1
0002	STORE_1	0002A27051	SEGMENT_3	0002052	DEPARTMENT_2
0002	STORE_1	0002A27051	SEGMENT_3	0002053	DEPARTMENT_3
0002	STORE_1	0002A27051	SEGMENT_3	0002070	DEPARTMENT_9
0002	STORE_1	0002A27051	SEGMENT_3	0002071	DEPARTMENT_10
0002	STORE_1	0002A27051	SEGMENT_3	0002072	DEPARTMENT_11
0002	STORE_1	0002A27051	SEGMENT_3	0002073	DEPARTMENT_12
0002	STORE_1	0002A27051	SEGMENT_3	0002080	DEPARTMENT_13
0002	STORE_1	0002A27051	SEGMENT_3	0002081	DEPARTMENT_14
0002	STORE_1	0002A27051	SEGMENT_3	0002082	DEPARTMENT_15
0002	STORE_1	0002A27051	SEGMENT_3	0002083	DEPARTMENT_16
0002	STORE_1	0002A27051	SEGMENT_3	0002084	DEPARTMENT_17
0002	STORE_1	0002A27051	SEGMENT_3	0002085	DEPARTMENT_18
0002	STORE_1	0002A27051	SEGMENT_3	0002086	DEPARTMENT_19
0002	STORE_1	0002A34099	SEGMENT_4	0002099	DEPARTMENT_20

 What i would like to show is the sales per segment like this

COD_STORE	DES_STORE	COD_SEGMENT	DES_SEGMENT	SALES
0002	STORE_1	0002A23066	SEGMENT_1	$       365.394,00
0002	STORE_1	0002A24062	SEGMENT_2	$       512.040,00
0002	STORE_1	0002A27051	SEGMENT_3	$   2.495.266,00
0002	STORE_1	0002A34099	SEGMENT_4	$   2.495.266,00

and not like this

COD_STORE	DES_STORE	COD_SEGMENT	DES_SEGMENT	SALES
0002	STORE_1	0002A23066	SEGMENT_1	$       365.394,00
0002	STORE_1	0002A24062	SEGMENT_2	$       512.040,00
0002	STORE_1	0002A27051	SEGMENT_3	$ 19.067.811,00
0002	STORE_1	0002A34099	SEGMENT_4	$   2.495.266,00

Any suggestion?

Thanks

[Stevo164 · Accepted solution]

I solved it creating the following measures:

• Check_tot1= CALCULATE(COUNT('Sales'[Sales]);'TRANSCODING_DEP_SEG';'TRANSCONDING_DEP_SEG'[CONDITION_RED]="1")
• Sales_Dep13= CALCULATE(SUM('SALES'[SALES]);'SALES'[DES_DEPARTMENT])="13")

Whit the two new measures I've finally created the measure that can solve my problem:

• Sales_Seg = IF('SALES'[Check_tot1]>0;'SALES'[Sales_Dep13];SUM('SALES[SALES]))

Thanks everyone for the support.

[Anonymous]

Hi @Stevo164,

Is there an indicator/column that indicates what should be red? If yes, you could use that condition as filter.

For example: Calculate(Sum(Table[Sales]), filter(Table, Table(Condition)....)

Does this help?

[Stevo164]

Hi @Anonymous,

thank you for your help.

I've the following tables 

• SALES with the sales per department
• TRANSCODING_DEP_SEG which defines the departments as a part of a segment

I've created a new table called DEPARTMENTS which defines what departments are red by the column CONDITION_RED

COD_STORE	DES_STORE	COD_SEGMENT	DES_SEGMENT	COD_DEPARTMENT	DES_DEPARTMENT	CONDITION_RED
0002	STORE_1	0002A23066	SEGMENT_1	0002060	DEPARTMENT_4	0
0002	STORE_1	0002A23066	SEGMENT_1	0002066	DEPARTMENT_8	0
0002	STORE_1	0002A24062	SEGMENT_2	0002061	DEPARTMENT_5	0
0002	STORE_1	0002A24062	SEGMENT_2	0002062	DEPARTMENT_6	0
0002	STORE_1	0002A24062	SEGMENT_2	0002064	DEPARTMENT_7	0
0002	STORE_1	0002A27051	SEGMENT_3	0002051	DEPARTMENT_1	0
0002	STORE_1	0002A27051	SEGMENT_3	0002052	DEPARTMENT_2	0
0002	STORE_1	0002A27051	SEGMENT_3	0002053	DEPARTMENT_3	0
0002	STORE_1	0002A27051	SEGMENT_3	0002070	DEPARTMENT_9	0
0002	STORE_1	0002A27051	SEGMENT_3	0002071	DEPARTMENT_10	0
0002	STORE_1	0002A27051	SEGMENT_3	0002072	DEPARTMENT_11	0
0002	STORE_1	0002A27051	SEGMENT_3	0002073	DEPARTMENT_12	0
0002	STORE_1	0002A27051	SEGMENT_3	0002080	DEPARTMENT_13	1
0002	STORE_1	0002A27051	SEGMENT_3	0002081	DEPARTMENT_14	1
0002	STORE_1	0002A27051	SEGMENT_3	0002082	DEPARTMENT_15	1
0002	STORE_1	0002A27051	SEGMENT_3	0002083	DEPARTMENT_16	1
0002	STORE_1	0002A27051	SEGMENT_3	0002084	DEPARTMENT_17	1
0002	STORE_1	0002A27051	SEGMENT_3	0002085	DEPARTMENT_18	1
0002	STORE_1	0002A27051	SEGMENT_3	0002086	DEPARTMENT_19	1
0002	STORE_1	0002A34099	SEGMENT_4	0002099	DEPARTMENT_20	1

I've tried your solution but the result isn't what I need

CALCULATE(SUM(SALES[SALES]);DEPARTMENTS[CONDITION_RED]="1")

COD_STORE	DES_STORE	COD_SEGMENT	DES_SEGMENT	SALES
0002	STORE_1	0002A23066	SEGMENT_1
0002	STORE_1	0002A24062	SEGMENT_2
0002	STORE_1	0002A27051	SEGMENT_3	$ 19.067.811,00
0002	STORE_1	0002A34099	SEGMENT_4	$   2.495.266,00

[Anonymous]

Hi @Stevo164,

Try CALCULATE(Average(SALES[SALES]);DEPARTMENTS[CONDITION_RED]="1")

[Stevo164]

Hi @Anonymous;

I've already tried this solution but it doesn't work for me, beacause I have daily sales grouped by week.

It would show the average sales per day.

[Anonymous]

@Stevo164,

Create measure using DAX below.

Measure  = IF(MAX(TRANSCODING_DEP_SEG[CONDITION_RED])=1;MAX(Sales[SALES]);SUM(Sales[SALES]))

Regards,
Lydia

[Stevo164]

Thank you @Anonymous for your kind answer, but it's not working for me, because I have the sales per day and it will show the max sales in a day.

[Anonymous]

@Stevo164,

I make a test using your sample data, you can check this PBIX file. If you still have questions about the DAX, please share your PBIX file via Private Message, I will test it in my environment.

Regards,
Lydia