Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

 

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.

Need Help

4 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks Frank, will get back

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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