Calculated Field in matrix Table and sum totals

[JM87Power]

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.

[v-yuezhe-msft]

@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]

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

[v-yuezhe-msft]

@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

[JM87Power]

Sorry for being unclear.

I am expecting to have the divider used by the period.

So therefore, I am expecting that 201801 all is divided by 1000, and for 201802 all is split by 500.

In the example, you see that everything has been split by 500. (first/lowest number?)

And then, because I use slicers, the example is taking it as a total. But in essence when I select on Location A in the slicer, I am expecting the divider in 201801 to use the Volume for only that location A. (650 in the example). when I select them both, I expect to have 1000 as divider.