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

[Anonymous]

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).

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 5 day numbers.

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

[v-frfei-msft]

Hi @Anonymous ,

To create a measure as below.

Measure = 
VAR a =
    CALCULATE (
        SUM ( 'Table'[VALUE] ),
        FILTER ( 'Table', 'Table'[category] <> "B" )
    ) + 0
VAR b =
    CALCULATE (
        SUM ( 'Table'[VALUE] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[category] = "C" )
    )
RETURN
    IF ( b <= 225, a, SUM ( 'Table'[VALUE] ) )

Pbix as attached.

[Anonymous]

Thanks Frank, will get back

[Anonymous]

Hello Frank,

The Matrix has only column groups; the Category column which you have assumed does not exist, i.e. there are no row groups in the matrix. There are a number of Measures (A,B,C,X,Y,Z) which are added as Values for the matrix.

Let me know if this can be worked out.

I tried using ISFILTERED at the day number level; but having some filter context challenges

[v-frfei-msft]

Hi @Anonymous ,

Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.