Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I am trying to achieve a measure that calculates the total of column 'value' but excludes certain rows based on multiple conditions because if I Calculate the total of the column, there will be some double counting. The double counting only occurs when the month is still open, and the columns through which this issue can be identified in are in Document type (if it's equal to SD) and Purchase order Description (if it begins on an "@")
The original table is much larger with other rows and columns but I have just attached the higligths.
For starters, I have created a measure (Month(Today)) so that PowerBI always knows which month it is, but that's how far I got when trying to use the CALCULATE and IF DAXs.
My problem is that Calculate is not compatible with TRUE/FALSE statements, i.e., (Month(Today)) and IF statements cannot refer to a column, only a measure, as far as I undestand.
What I need is an automate formula than can calculate the total of the value column but exclude any row of the total IF you're in the current (open month) and Document type = SD or Purchase Order Description starts with "@"
Is this possible or do I have to perform multiple steps? i.e. adding a new column to isolate the "@" or any other step?
The inverse would also be sufficient, namely sum all the rows in the Value column if current month = Month Number AND Docuemnt type = "SD" / Product Description order starts with "@" and I could just deduct this value from the total.
Solved! Go to Solution.
hi @Newbie_1
Do you mean your Month Number column is text format,so just adjust the formula as below with VALUE Function
Measure =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
Table,
MONTH ( TODAY () ) = VALUE(Table[Month Number])
&& Table[Document Type] = "SD"
&& LEFT ( Table[Purchase Order Description], 1 ) = "@"
)
)
Regards,
Lin
hi @Newbie_1
You try this measure
Measure =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
Table,
MONTH ( TODAY () ) = Table[Month Number]
&& Table[Document Type] = "SD"
&& LEFT ( Table[Purchase Order Description], 1 ) = "@"
)
)
Regards,
Lin
Thanks a lot! This worked like a charm!
Thank you for the effort, unfortunately this generated the error I have been facing before, namely that I get an error because DAX does not allow the comparison of Values of Interger with Values of type text. Probably it's my dataset that prohibits it but the solution below help me to mediate it!
hi @Newbie_1
Do you mean your Month Number column is text format,so just adjust the formula as below with VALUE Function
Measure =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
Table,
MONTH ( TODAY () ) = VALUE(Table[Month Number])
&& Table[Document Type] = "SD"
&& LEFT ( Table[Purchase Order Description], 1 ) = "@"
)
)
Regards,
Lin
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |