Hello everybody,i have the following table SALES that shows the sales per department.COD_STOREDES_STORECOD_DEPARTMENTDES_DEPARTMENTSALESDATE0002STORE_10002051DEPARTMENT_1 $ 1.068.811201801020002STORE_10002052DEPARTMENT_2 $ 77.611201801020002STORE_10002053DEPARTMENT_3 $ 405.608201801020002STORE_10002060DEPARTMENT_4 $ 84.680201801020002STORE_10002061DEPARTMENT_5 $ 222.855201801020002STORE_10002062DEPARTMENT_6 $ 207.413201801020002STORE_10002064DEPARTMENT_7 $ 81.772201801020002STORE_10002066DEPARTMENT_8 $ 280.714201801020002STORE_10002070DEPARTMENT_9 $ 23.850201801020002STORE_10002071DEPARTMENT_10 $ 12.693201801020002STORE_10002072DEPARTMENT_11 $ 3.252201801020002STORE_10002073DEPARTMENT_12 $ 9.124201801020002STORE_10002080DEPARTMENT_13 $ 2.495.266201801020002STORE_10002081DEPARTMENT_14 $ 2.495.266201801020002STORE_10002082DEPARTMENT_15 $ 2.495.266201801020002STORE_10002083DEPARTMENT_16 $ 2.495.266201801020002STORE_10002084DEPARTMENT_17 $ 2.495.266201801020002STORE_10002085DEPARTMENT_18 $ 2.495.266201801020002STORE_10002086DEPARTMENT_19 $ 2.495.266201801020002STORE_10002099DEPARTMENT_20 $ 2.495.26620180102 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_STOREDES_STORECOD_SEGMENTDES_SEGMENTCOD_DEPARTMENTDES_DEPARTMENT0002STORE_10002A23066SEGMENT_10002060DEPARTMENT_40002STORE_10002A23066SEGMENT_10002066DEPARTMENT_80002STORE_10002A24062SEGMENT_20002061DEPARTMENT_50002STORE_10002A24062SEGMENT_20002062DEPARTMENT_60002STORE_10002A24062SEGMENT_20002064DEPARTMENT_70002STORE_10002A27051SEGMENT_30002051DEPARTMENT_10002STORE_10002A27051SEGMENT_30002052DEPARTMENT_20002STORE_10002A27051SEGMENT_30002053DEPARTMENT_30002STORE_10002A27051SEGMENT_30002070DEPARTMENT_90002STORE_10002A27051SEGMENT_30002071DEPARTMENT_100002STORE_10002A27051SEGMENT_30002072DEPARTMENT_110002STORE_10002A27051SEGMENT_30002073DEPARTMENT_120002STORE_10002A27051SEGMENT_30002080DEPARTMENT_130002STORE_10002A27051SEGMENT_30002081DEPARTMENT_140002STORE_10002A27051SEGMENT_30002082DEPARTMENT_150002STORE_10002A27051SEGMENT_30002083DEPARTMENT_160002STORE_10002A27051SEGMENT_30002084DEPARTMENT_170002STORE_10002A27051SEGMENT_30002085DEPARTMENT_180002STORE_10002A27051SEGMENT_30002086DEPARTMENT_190002STORE_10002A34099SEGMENT_40002099DEPARTMENT_20 What i would like to show is the sales per segment like this COD_STOREDES_STORECOD_SEGMENTDES_SEGMENTSALES0002STORE_10002A23066SEGMENT_1 $ 365.394,000002STORE_10002A24062SEGMENT_2 $ 512.040,000002STORE_10002A27051SEGMENT_3 $ 2.495.266,000002STORE_10002A34099SEGMENT_4 $ 2.495.266,00 and not like this COD_STOREDES_STORECOD_SEGMENTDES_SEGMENTSALES0002STORE_10002A23066SEGMENT_1 $ 365.394,000002STORE_10002A24062SEGMENT_2 $ 512.040,000002STORE_10002A27051SEGMENT_3 $ 19.067.811,000002STORE_10002A34099SEGMENT_4 $ 2.495.266,00 Any suggestion? Thanks

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.

Stevo164

Regular Visitor

8 years ago

Solved

selective sum

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
8 years ago
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.

8 Replies

Anonymous

Not applicable

8 years ago

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

Regular Visitor

8 years ago

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
Not applicable
8 years ago
Hi Stevo164,

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

Stevo164
Regular Visitor
8 years ago
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.

Forum Discussion

selective sum

8 Replies

Related content

Cumulative sum less than selected month

Sum dynamic parameter fields multiple selected parameter

Running sum

Filtering a Sum

Improper Sum with slicer