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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply

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

1 ACCEPTED SOLUTION
VahidDM
Super User
Super User

Hi @jorge_oliveira 

 

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:

VahidDM_0-1642375881905.png

 

 

 

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/

 

 

View solution in original post

5 REPLIES 5

Hello,

 

I'm still working on this formula and need extra help.

I have the following model:

 

jorge_oliveira_0-1644343943516.png

 

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])
return
CALCULATE([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])+0
 
What 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.
jorge_oliveira_1-1644344417016.png

 

jorge_oliveira_2-1644344427253.png

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

VahidDM
Super User
Super User

Hi @jorge_oliveira 

 

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:

VahidDM_0-1642375881905.png

 

 

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.