Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Stevo164
Regular Visitor

selective sum

‎03-29-2018 08:56 AM

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

Message 1 of 9
618 Views
0
1 ACCEPTED SOLUTION
Stevo164
Regular Visitor

‎04-04-2018 01:16 AM

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

Message 9 of 9
604 Views
0
8 REPLIES 8
Stevo164
Regular Visitor

‎04-04-2018 01:16 AM

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.

Message 9 of 9
605 Views
0
Anonymous
Not applicable

‎03-29-2018 09:38 AM

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?

Message 2 of 9
614 Views
0
Stevo164
Regular Visitor
In response to Anonymous

‎03-29-2018 10:03 AM

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
Message 3 of 9
607 Views
0
Anonymous
Not applicable
In response to Stevo164

‎03-29-2018 10:13 AM

Hi @Stevo164,

 

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

Message 4 of 9
603 Views
0
Stevo164
Regular Visitor
In response to Anonymous

‎03-29-2018 10:25 AM

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.

Message 5 of 9
599 Views
0
v-yuezhe-msft
Employee
Employee
In response to Stevo164

‎04-03-2018 01:43 AM

@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.
Message 6 of 9
579 Views
0
Stevo164
Regular Visitor
In response to v-yuezhe-msft

‎04-04-2018 12:56 AM

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.

 

Message 7 of 9
571 Views
0
v-yuezhe-msft
Employee
Employee
In response to Stevo164

‎04-04-2018 01:16 AM

@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.
Message 8 of 9
567 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All