Forum Discussion
Filter measures
Hello,
I'm relatively new to Power BI. I'm stuck with a formula for some time now so any help would be appreciated.
Is it possible to filter a measure with a number selected in a slicer and then stored in another measure?
I have two slicers: year and month, both are numbers. E.g. 2021 for year and 3 (March) for month.
I need two measures:
1 - Sum only for that year and month (3).
2- Cumulative total for that year up to month number 3.
My fact table looks something like this:
Products Year Month Value
A 2021 1 10
A 2021 2 10
A 2021 3 30
A 2021 4 10
B 2021 1 20
B 2021 2 10
B 2021 3 40
B 2021 4 10
C 2021 1 15
C 2021 2 10
C 2021 3 20
C 2021 4 10
The results I expect are these:
Products Measure 1 Measure 2
A 30 50
B 40 70
C 20 45
Thanks.
Jorge
Try these 2 measures:
Measure 1 = Var _Y = SELECTEDVALUE('Table'[Year]) Var _M = SELECTEDVALUE('Table'[Month]) return CALCULATE(SUM('Table'[Value]),filter(ALLEXCEPT('Table','Table'[Products]),'Table'[Year]=_Y&&'Table'[Month]=_M))Measure 2 = Var _Y = SELECTEDVALUE('Table'[Year]) Var _M = SELECTEDVALUE('Table'[Month]) return CALCULATE(SUM('Table'[Value]),filter(ALLEXCEPT('Table','Table'[Products]),'Table'[Year]=_Y&&'Table'[Month]<=_M))output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
5 Replies
Try these 2 measures:
Measure 1 = Var _Y = SELECTEDVALUE('Table'[Year]) Var _M = SELECTEDVALUE('Table'[Month]) return CALCULATE(SUM('Table'[Value]),filter(ALLEXCEPT('Table','Table'[Products]),'Table'[Year]=_Y&&'Table'[Month]=_M))Measure 2 = Var _Y = SELECTEDVALUE('Table'[Year]) Var _M = SELECTEDVALUE('Table'[Month]) return CALCULATE(SUM('Table'[Value]),filter(ALLEXCEPT('Table','Table'[Products]),'Table'[Year]=_Y&&'Table'[Month]<=_M))output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/Hi VahidDM,
Thanks. It almost works. And that's because I've simplified one thing that I thought it wouldn't affect.
I have a dimension table to Products and to the resulting table I'm using the field from DIM_PRODUCTS. I need this, because there's a third table with the expected quantity for products and it could exist Porduct D on that table and not on the first table.
So the exact model is:
[TABLE A]
Products Year Month Value
A 2021 1 10
A 2021 2 10
A 2021 3 30
A 2021 4 10
B 2021 1 20
B 2021 2 10
B 2021 3 40
B 2021 4 10
C 2021 1 15
C 2021 2 10
C 2021 3 20
C 2021 4 10
[TABLE B]
Products Year Month Value
A 2021 1 10
A 2021 2 10
A 2021 3 30
A 2021 4 10
B 2021 1 20
B 2021 2 10
B 2021 3 40
B 2021 4 10
C 2021 1 15
C 2021 2 10
C 2021 3 20
C 2021 4 10
D 2021 1 15
D 2021 2 10
D 2021 3 20
D 2021 4 10
[DIM_PRODUCTS]
Products Value
A 5
B 5
C 5
D 5
Table A and Table B are both related to DIM_PRODUCTS.
In the results table I will also have two measures using Table B, identical to the other two measures, so I would expect something like this:
The results I expect are these:
Products Measure 1 Measure 2 Measure 3 Measure 4
A 30 50 30 50
B 40 70 40 70
C 20 45 20 45
D 0 0 20 45
Hello,
I'm still working on this formula and need extra help.
I have the following model:
I need the Cumulative total for the selected year/month and I'm using the formula:
AMT_BALANCE_YTD =VAR YEAR = SELECTEDVALUE(PROFIT_CENTER[YEAR])VAR MONTH = SELECTEDVALUE(PROFIT_CENTER[MONTH])returnCALCULATE([Accumulated Balance];FILTER(ALLEXCEPT(PROFIT_CENTER;DIM_STRUCTURE);PROFIT_CENTER[YEAR] = YEAR && PROFIT_CENTER[MONTH] <= MONTH))The measure [Accumulated Balance] is: [Accumulated Balance] = SUM(PROFIT_CENTER[AMT_BALANCE])+0What isn't working is when I select a month that doesn't have any value to a specific ID_COST_CENTER / ID_ACCOUNT, as in the example below (in green what I expect to see in a card depending on the year/month selected and in red what I'm getting with the formula I'm using.How can I change the formula to obtain the desired results?
THanks for your help.
Jorge
Hi VahidDM ,
I apologize for the insistence or if I'm being intrusive, but is there any change I can make to the measure to get the desired results as I described in the previous message?
For example, in May the result should be 4306 and not zero.
Thanks.
Jorge
