cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Stevo164
Regular Visitor

selective sum

Hello everybody,

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

.

COD_STOREDES_STORECOD_DEPARTMENTDES_DEPARTMENTSALESDATE
0002STORE_10002051DEPARTMENT_1 $ 1.068.81120180102
0002STORE_10002052DEPARTMENT_2 $      77.61120180102
0002STORE_10002053DEPARTMENT_3 $    405.60820180102
0002STORE_10002060DEPARTMENT_4 $      84.68020180102
0002STORE_10002061DEPARTMENT_5 $    222.85520180102
0002STORE_10002062DEPARTMENT_6 $    207.41320180102
0002STORE_10002064DEPARTMENT_7 $      81.77220180102
0002STORE_10002066DEPARTMENT_8 $    280.71420180102
0002STORE_10002070DEPARTMENT_9 $      23.85020180102
0002STORE_10002071DEPARTMENT_10 $      12.69320180102
0002STORE_10002072DEPARTMENT_11 $         3.25220180102
0002STORE_10002073DEPARTMENT_12 $         9.12420180102
0002STORE_10002080DEPARTMENT_13 $ 2.495.26620180102
0002STORE_10002081DEPARTMENT_14 $ 2.495.26620180102
0002STORE_10002082DEPARTMENT_15 $ 2.495.26620180102
0002STORE_10002083DEPARTMENT_16 $ 2.495.26620180102
0002STORE_10002084DEPARTMENT_17 $ 2.495.26620180102
0002STORE_10002085DEPARTMENT_18 $ 2.495.26620180102
0002STORE_10002086DEPARTMENT_19 $ 2.495.26620180102
0002STORE_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_DEPARTMENT
0002STORE_10002A23066SEGMENT_10002060DEPARTMENT_4
0002STORE_10002A23066SEGMENT_10002066DEPARTMENT_8
0002STORE_10002A24062SEGMENT_20002061DEPARTMENT_5
0002STORE_10002A24062SEGMENT_20002062DEPARTMENT_6
0002STORE_10002A24062SEGMENT_20002064DEPARTMENT_7
0002STORE_10002A27051SEGMENT_30002051DEPARTMENT_1
0002STORE_10002A27051SEGMENT_30002052DEPARTMENT_2
0002STORE_10002A27051SEGMENT_30002053DEPARTMENT_3
0002STORE_10002A27051SEGMENT_30002070DEPARTMENT_9
0002STORE_10002A27051SEGMENT_30002071DEPARTMENT_10
0002STORE_10002A27051SEGMENT_30002072DEPARTMENT_11
0002STORE_10002A27051SEGMENT_30002073DEPARTMENT_12
0002STORE_10002A27051SEGMENT_30002080DEPARTMENT_13
0002STORE_10002A27051SEGMENT_30002081DEPARTMENT_14
0002STORE_10002A27051SEGMENT_30002082DEPARTMENT_15
0002STORE_10002A27051SEGMENT_30002083DEPARTMENT_16
0002STORE_10002A27051SEGMENT_30002084DEPARTMENT_17
0002STORE_10002A27051SEGMENT_30002085DEPARTMENT_18
0002STORE_10002A27051SEGMENT_30002086DEPARTMENT_19
0002STORE_10002A34099SEGMENT_40002099DEPARTMENT_20

 

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

 

COD_STOREDES_STORECOD_SEGMENTDES_SEGMENTSALES
0002STORE_10002A23066SEGMENT_1 $       365.394,00
0002STORE_10002A24062SEGMENT_2 $       512.040,00
0002STORE_10002A27051SEGMENT_3 $   2.495.266,00
0002STORE_10002A34099SEGMENT_4 $   2.495.266,00

 

and not like this

 

COD_STOREDES_STORECOD_SEGMENTDES_SEGMENTSALES
0002STORE_10002A23066SEGMENT_1 $       365.394,00
0002STORE_10002A24062SEGMENT_2 $       512.040,00
0002STORE_10002A27051SEGMENT_3 $ 19.067.811,00
0002STORE_10002A34099SEGMENT_4 $   2.495.266,00

 

Any suggestion?

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Stevo164
Regular Visitor

Re: selective sum

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.

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

Re: selective sum

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

Re: selective sum

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_STOREDES_STORECOD_SEGMENTDES_SEGMENTCOD_DEPARTMENTDES_DEPARTMENTCONDITION_RED
0002STORE_10002A23066SEGMENT_10002060DEPARTMENT_40
0002STORE_10002A23066SEGMENT_10002066DEPARTMENT_80
0002STORE_10002A24062SEGMENT_20002061DEPARTMENT_50
0002STORE_10002A24062SEGMENT_20002062DEPARTMENT_60
0002STORE_10002A24062SEGMENT_20002064DEPARTMENT_70
0002STORE_10002A27051SEGMENT_30002051DEPARTMENT_10
0002STORE_10002A27051SEGMENT_30002052DEPARTMENT_20
0002STORE_10002A27051SEGMENT_30002053DEPARTMENT_30
0002STORE_10002A27051SEGMENT_30002070DEPARTMENT_90
0002STORE_10002A27051SEGMENT_30002071DEPARTMENT_100
0002STORE_10002A27051SEGMENT_30002072DEPARTMENT_110
0002STORE_10002A27051SEGMENT_30002073DEPARTMENT_120
0002STORE_10002A27051SEGMENT_30002080DEPARTMENT_131
0002STORE_10002A27051SEGMENT_30002081DEPARTMENT_141
0002STORE_10002A27051SEGMENT_30002082DEPARTMENT_151
0002STORE_10002A27051SEGMENT_30002083DEPARTMENT_161
0002STORE_10002A27051SEGMENT_30002084DEPARTMENT_171
0002STORE_10002A27051SEGMENT_30002085DEPARTMENT_181
0002STORE_10002A27051SEGMENT_30002086DEPARTMENT_191
0002STORE_10002A34099SEGMENT_40002099DEPARTMENT_201

 

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

 

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

 

COD_STOREDES_STORECOD_SEGMENTDES_SEGMENTSALES
0002STORE_10002A23066SEGMENT_1 
0002STORE_10002A24062SEGMENT_2 
0002STORE_10002A27051SEGMENT_3 $ 19.067.811,00
0002STORE_10002A34099SEGMENT_4 $   2.495.266,00
Anonymous
Not applicable

Re: selective sum

Hi @Stevo164,

 

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

Stevo164
Regular Visitor

Re: selective sum

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.

Microsoft
Microsoft

Re: selective sum

@Stevo164,

Create measure using DAX below.

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


Capture.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Stevo164
Regular Visitor

Re: selective sum

Thank you @v-yuezhe-msft 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.

 

Microsoft
Microsoft

Re: selective sum

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Stevo164
Regular Visitor

Re: selective sum

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.

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors