Forum Discussion

mic_rys's avatar
mic_rys
Icon for Helper I rankHelper I
2 years ago
Solved

How to Sum values in the same section - including filtering

Hi,

I have a problem with the DAX mesaure and I'm looking for the dax master 🙂

my table:

CustomerTask IDTabNameFieldNameValue txtValue int
A11_1PromoName 1Cars 
A11_1Quantity 1 2
A11_1Avail 1No 
A11_2PromoName 2Bikes 
A11_2Quantity 2 1
A11_2Avail 2  
A11_3PromoName 3Moto 
A11_3Quantity 3 4
A11_3Avail 3Yes 
A21_1PromoName 1Cars 
A21_1Quantity 1 2
A21_1Avail 1No 
A21_2PromoName 2Bikes 
A21_2Quantity 2 5
A21_2Avail 2  
A21_3PromoName 3Moto 
A21_3Quantity 3 4
A21_3Avail 3Yes 
B     
B     
etc     

 

my case:

In the report is Slicer/filter which select Promo name from column Valuetxt (created as Starts with "Promo").

In exaple will be: Cars, Bikes, Moto.

 

problem:

1. how to sum column Value int for selected eg. Cars with the same Tab ID (1_1 etc)

2. how to sum column Value int for selected eg. Cars with the same Tab ID (1_1 etc) and TaskID (1 etc)

 

Reports columns (selected promo = Cars)

1. Customer

2. TaskID

3. Sum Value int for the Cars

---

1. Customer

2. Sum Value int for the Cars

 

In short; I need to sum up Value int for the selected promotion from the same TabID and TaskID then summarize for Customer, Task OR only Customer.

 

Thanks a lot for the help !:)

 

 

 

  • FreemanZ's avatar
    FreemanZ
    2 years ago

    hi mic_rys ,

     

    not sure if i really get you, please try:

    1) plot a slicer with data[Value txt];

    2) plot a table visual with data[customer] and/or data[task ID] and a measure like:

    measure = 
VAR _txt = SELECTEDVALUE(data[Value txt])
VAR _tabname = MAX(data[TabName])
VAR _result =
CALCULATE(
    SUM(data[Value int]),
    ALL(data[Value txt]),
    FILTER(
        ALL(data[TabName]),
        data[TabName]=_tabname
    )
)
RETURN _result

     

    it worked like:

     

     

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    2 years ago

    hi mic_rys ,

     

    not sure if i really get you, please try:

    1) plot a slicer with data[Value txt];

    2) plot a table visual with data[customer] and/or data[task ID] and a measure like:

    measure = 
VAR _txt = SELECTEDVALUE(data[Value txt])
VAR _tabname = MAX(data[TabName])
VAR _result =
CALCULATE(
    SUM(data[Value int]),
    ALL(data[Value txt]),
    FILTER(
        ALL(data[TabName]),
        data[TabName]=_tabname
    )
)
RETURN _result

     

    it worked like:

     

     

    • mic_rys's avatar
      mic_rys
      Icon for Helper I rankHelper I
      2 years ago

      thanks a lot! I did a little modification in the measure:


      VAR       _promo = SELECTEDVALUE(Tasks[Value])
      VAR _section = VALUES(Tasks[Tab])
      VAR _result =
      CALCULATE(
                  SUM(Tasks[Value int]),
                  ALL(Tasks[Value]),
                  FILTER(
                      ALL(Tasks[Tab]),
                      Tasks[Tab] in _section
                  )
              )
      RETURN
          _result
       
      because can appear more than 1 tabname in one taskid and now a have another problem because in the total measure shows wrong values.

      In total measure add values to taskID from the other which exists the same TabName; but not selected in the slicer (Train).
       

       

      Could you help me?:)
      (how to add a pbi project to the post?:))