Search code examples
matrixpowerbidaxpowerbi-desktopmeasure

Microsoft Power BI Matrix - change individual cell value based on column total

I have a Power BI Matrix in which there are a number of measures. I have a lot of transaction level data at the back-end for each day in a month.

I have all the months in a Slicer, in which I choose one month at a time.

I develop measures X, Y, Z, A, B, C to compute summarized data.

I use a matrix, in which the day numbers of a month are shown in columns (I have shown only day number 1-5 as an example, instead of 1-30 or 1-31).

enter image description here

You can see the Power BI report in an Excel. (I have some restriction is showing the actual report itself.)

I have to implement the below logic between Measure B and Measure C:

If C (MonthTotal) < 225, then B = 0

The above equation works fine. I am able to get the right value of B at the month level. I have written some DAX function to accomplish this. This said, the individual day level summarized values for B are 12, 15, 10, 15, 8 (these are the original summarrized values at the day level).

However, I do not want the values 12, 15, 10, 15, 8 for Measure B at the day level.

Rather, I want 0, 0, 0, 0, 0 for Day number 1 to Day number 5 for Measure B.

The value for B is correct at the summarized level (all 5 days in a month). The Matrix column (day number) is not computing the B value at the day level using the logic based on the C value. Instead it is using the original values i.e. I do not want 12, 15, 10, 15, 8 for day number 1-5. I want 0s for all the five day numbers.

Can you help me accomplish this ? Am okay in choosing some custom visuals too, if that is the only option.

Solution

  • You can modify the definition of B to change the result depending on the drill-down level.

    There would be several approaches possible, depending on your requirement.

    • Calculate a result unless filtered/sliced on daily level. Return 0 otherwise.
    • Calculate a result only when filtered/sliced on monthly level. Return 0 otherwise.

    Assuming the first one is the desired behavior, the modified DAX formula of measure B will be something like below:

    B = IF (
    ISFILTERED ( 'Calendar'[Date] ),
    0,
    <expression of the actual calculation>
)