Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a view that contains info similar to the table below and to display this data I’m using a matrix.
The columns that are NOT marked with YTD can be summarized normally using the sum function
Columns that Are YTD can either be one of two things based on what date the user selected. if the user selects no dates then the maximum date is used and the totals within the matrix would be 6, 30, 10 for plan YTD, actual YTD and last week YTD. If the user selects 7/25/2020 the total will be 75, 100 30.
but if the user selects BOTH 7/25 and 8/8 then only the result for the date 8/8 should show
week# | Product | Plan | Actual | last Week | date | Plan YTD | Actual YTD | last Week YTD |
30 | A | 15 | 5 | 1 | 7/25/2020 | 20 | 50 | 30 |
30 | B | 0 | 1 | 1 | 7/25/2020 | 25 | 50 | 0 |
30 | C | 15 | 5 | 0 | 7/25/2020 | 30 | 0 | 0 |
31 | A | 0 | 0 | 2 | 8/1/2020 | 10 | 25 | 20 |
31 | B | 0 | 0 | 2 | 8/1/2020 | 11 | 25 | 0 |
31 | C | 10 | 5 | 0 | 8/1/2020 | 12 | 0 | 0 |
32 | A | 9 | 3 | 0 | 8/8/2020 | 1 | 0 | 10 |
32 | B | 0 | 3 | 3 | 8/8/2020 | 2 | 20 | 0 |
32 | C | 0 | 0 | 3 | 8/8/2020 | 3 | 10 | 0 |
I’m using this formula in one of the computed columns
calc_ACTUAL_YTD = IF(Query1[week#] = [Measure], Query1[ActualYTD], 0)
for the Measure I have tried these but none seem to work
Measure = CALCULATE( MAX(Query1[week#]), ALLSELECTED(Query1))
Measure = WEEKNUM(LASTDATE(ALLSELECTED(Query1[Date])))
Measure = MAX(Query1[week#])
But I either get the maximum date value or I get the week value that I need but all values in the [calc_ACTUAL_YTD] column is 0 except for the max date or it still sums everything up
Solved! Go to Solution.
Hi @Roy_B ,
Like this?
--Before filtering the [date] column
--After filtering the [date] column
__A_YTD =
VAR x = MAXX( FILTER( ALL(Sheet3), Sheet3[Product] = MAX(Sheet3[Product]) ), [date] )
VAR y =
CALCULATE(
SUM([Actual YTD]),
FILTER(
ALL(Sheet3),
Sheet3[date] = x
)
)
RETURN
IF(
ISFILTERED(Sheet3[date]),
CALCULATE(
SUM(Sheet3[Actual YTD]),
FILTER(
Sheet3,
Sheet3[date] = x
)
),
y
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
HI @Roy_B
try something like
CALCULATE (
COUNTROWS('Raw Data'),
FILTER ( ALLEXCEPT('Raw Data','Raw Data'[Decision]),'Raw Data'[Decision]="Award")
)
or
VAR SearchValue = <Search_Value>
RETURN
CALCULATE (
SELECTEDVALUE ( <Result_ColumnName>, <Alternate_Result> ),
FILTER (
ALLNOBLANKROW ( <Search_ColumnName> ),
<Search_ColumnName> == SearchValue -- The == operator distinguishes between blank and 0/empty string
),
ALL ( <table_of_Result_ColumnName> ) -- If Result_ColumnName is t, this is ALL ( t )
)
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
Filter value is not known on design time.
What ever fomula I use it must only get the maximum value from the seelcted filters
Hi @Roy_B ,
Like this?
--Before filtering the [date] column
--After filtering the [date] column
__A_YTD =
VAR x = MAXX( FILTER( ALL(Sheet3), Sheet3[Product] = MAX(Sheet3[Product]) ), [date] )
VAR y =
CALCULATE(
SUM([Actual YTD]),
FILTER(
ALL(Sheet3),
Sheet3[date] = x
)
)
RETURN
IF(
ISFILTERED(Sheet3[date]),
CALCULATE(
SUM(Sheet3[Actual YTD]),
FILTER(
Sheet3,
Sheet3[date] = x
)
),
y
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |