topic A field parameter produce wrong percentage values when nested in matrix rows in DAX Commands and Tips

A field parameter produce wrong percentage values when nested in matrix rows

Pietro_Noli — Sun, 21 Jan 2024 13:56:57 GMT

I have a Power BI report (modeled using a star schema) in which I have created:

1) a field parameter, with eight different data fields;

2) a calculation group, that contains the following time calculation items:
a) Current year values (= "CY")
b) The running-sum values of the current year values (= "CY Cumulate")

c) The percentage of the different current year values against the total value of the current year (= "CY Vertical %")

In Report View I have created:

1) a multi-selection slicer with the field parameter;

2) a single-selection slicer with the year field;

3) a multi-selection slicer with the calculation items of the calculation group;

4) a matrix visual, having on rows the field parameter, on columns the calculation items and a measure (for example: quantity sold) as value.

The problem arises when I choose:

- two or more fields in the field parameter slicer (shown in the matrix row)s

- "CY Cumulate" or "CY Vertical %" in the slicer.

The problem is the fact that in the intersection between the "CY Cumulate" or"CY Vertical %" columns and the inner field in the rows, the values now are wrong and they wrongly depend on how many outer items in the rows are expanded, while instead their value should always be the same indipendently on how many items are expanded in the rows.

Can you please help me ?
Thanks in advance,

Pietro.

Re: A field parameter produce wrong percentage values when nested in matrix rows

some_bih — Sun, 21 Jan 2024 20:12:08 GMT

Hi Pietro_Noli so measure/s works when Quearter is not selected in field parameter slicer?

I notice that you have 8 possible values in field parameter slicer while 6 in measure CY Cumulate, part SWITCH (Quarter and Month are extras?)

Re: A field parameter produce wrong percentage values when nested in matrix rows

Pietro_Noli — Mon, 22 Jan 2024 07:57:51 GMT

Hi some_bih ,

you are right: in the "CY Cumulate" measure formula, I'm missing Quarter and Month sections.

The correct formula for the "CY Cumulate" measure formula is in the following image:

Thanks for your help.

Pietro.

Re: A field parameter produce wrong percentage values when nested in matrix rows

some_bih — Mon, 22 Jan 2024 08:56:45 GMT

Hi Pietro_Noli if this help please accept as Solution so other member of community could use it.

Thank you.

Re: A field parameter produce wrong percentage values when nested in matrix rows

Pietro_Noli — Mon, 22 Jan 2024 13:15:50 GMT

Hi some_bih ,

the problem is NOT solved.
Correcting the formula for the "CY Cumulate" measure does NOT give the correct values that I show in green color in a previous Excel image. I can still see the same values that are highlighted in yellow in that image.
Someone can help me?
Thanks in advance,

Pietro.