Forum Discussion

jorge_oliveira's avatar
jorge_oliveira
Helper I
4 years ago
Solved

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...
  • VahidDM's avatar
    VahidDM
    4 years ago

    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:

     

     

     

    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/

     

     

jorge_oliveira's avatar
jorge_oliveira
Helper I
4 years ago

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])
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.

 

How can I change the formula to obtain the desired results?

THanks for your help.

Jorge

 
jorge_oliveira's avatar
jorge_oliveira
Helper I
4 years ago

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