Forum Discussion

Fragan's avatar
Fragan
Helper III
6 years ago
Solved

Custom total calculation for one column value

Hey,

 

I have this table :

 

 

I made a matrix visual out of it :

 


What i want to do is change the totals to :

  • For  Attribute1 keep the totals
  • For Attribute2, totals = totals/2

 

I want to change the sub-totals and grand total for one attribute.

 

Anyone can help me with that please?

Need Help
  • Fragan's avatar
    Fragan
    6 years ago

    I found a solution :

     

     

    Mesure = SUMX( 'Data'; SWITCH('Data'[attributes];
"Attribut2";IF(HASONEVALUE('Data'[date]);CALCULATE(SUM(Data[value]);Data[attributes] = "Attribut2");CALCULATE(SUM(Data[value]);Data[attributes] = "Attribut2")/2);
var a = 'Data'[attributes] return
        CALCULATE ( SUM ( 'Data'[value] );'Data'[attributes]=a)
))

     

     

9 Replies

  • bheepatel's avatar
    bheepatel
    Resolver IV
    6 years ago

    Hi Fragan 

     

    In your first Table, you can add an additional column with the following formula:

     

    NewValue = IF(Attributes = "Attribute2", Value /12, Value)

     

    The above column will give you the values where if the Attribute is Attribute2, then it will divide the original value by 12. If it is not Attribute2, it will keep the original value as is. This should change your sub-totals and totals.

     

    You can use the NewValue column in your matrix visual.

     

    Hope that helps!

    • Fragan's avatar
      Fragan
      Helper III
      6 years ago

      bheepatel with your solution all Attribute2 values will be divided by 12

       

      amitchandak the idea seem good, but i want also the sub total of attribute2 to change :

       

       

      I want the 482 to be 241.

  • Fragan's avatar
    Fragan
    Helper III
    6 years ago

    I found a solution :

     

     

    Mesure = SUMX( 'Data'; SWITCH('Data'[attributes];
"Attribut2";IF(HASONEVALUE('Data'[date]);CALCULATE(SUM(Data[value]);Data[attributes] = "Attribut2");CALCULATE(SUM(Data[value]);Data[attributes] = "Attribut2")/2);
var a = 'Data'[attributes] return
        CALCULATE ( SUM ( 'Data'[value] );'Data'[attributes]=a)
))