Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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

 

PowerBiMatrixInDesktop.PNG

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.

4 REPLIES 4
v-frfei-msft
Community Support
Community Support

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

Capture.PNG

 

Pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

Thanks Frank, will get back

Anonymous
Not applicable

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

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.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.