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
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.
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.
Thanks Frank, will get back
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
100 | |
98 | |
88 | |
70 |
User | Count |
---|---|
166 | |
131 | |
129 | |
102 | |
98 |