Forum Discussion

Stevo164's avatar
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...
  • Stevo164's avatar
    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.

Anonymous's avatar
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's avatar
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_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's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Stevo164,

     

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

    • Stevo164's avatar
      Stevo164
      Regular Visitor
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        8 years ago

        Stevo164,

        Create measure using DAX below.

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




        Regards,
        Lydia