Forum Discussion

JM87Power's avatar
JM87Power
Regular Visitor
8 years ago

Calculated Field in matrix Table and sum totals

 

Hi,

 

I have a datatabel (see below, column until Value). This data is kept per period. I want to create the column called Create wereby the value is divided by the Volume. Which is one volume for the Period. I cannot get this fixed, as in the calculated column I have, the subtotals are added. So that for Cat B the total is 5.300 as value, but should still be divided by the 1000, but it is divided then by 2.000. I am not sure what I have to create to get the correct column. By the way below is not based on the Matrix I have but on the Data were the Matix is working with. Hopefully someone can help me.

Thanks a lot in advance.

 

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    JM87Power,

    Does the following DAX return your expected result?

    Create = Overview[Value]/CALCULATE(FIRSTNONBLANK(Overview[Value],1),FILTER(Overview,Overview[CAT A]="Volume"))



    Regards,
    Lydia

    • JM87Power's avatar
      JM87Power
      Regular Visitor
      8 years ago

      Hi Lydia,

       

      This is exactly what I wanted/expected. However, when I fill in the formula I get negative numbers.

       

      Is it possible because if have more columns in my data, wereby that is linked to a location, therefore I can have multiple Volume values. Even if I change location with a slicer, it still picks up the wrong value. When I add the data like below, it will split everything by 500, but not by the volume of the corresponding period. (have added 201802 for some rows).

       

      thnx in advance

      JM

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        JM87Power,

        Could you please post expected result based on  the new sample table you post? Would you like to split the value by Volume 500 or Volume 1000?

        Regards,
        Lydia