Forum Discussion

Tubster66's avatar
Tubster66
Regular Visitor
8 years ago
Solved

Summarize table with filters

Hi

 

Power BI newbie - apologies if this is rookie error stuff.

 

I have a SUMMARIZE table generated with Forecast = summarize(budget,Budget[Name],Budget[Period],Budget[Year],"forecast",(calculate(sum(Budget[Budget]),all(Budget[Period]))/12)).

 

I have done the above because I want to flatten the seasonal monthly budgets across the year.

 

I use a visual card to display the following measure:

 

msVarForecast = calculate(sum('Fees Billed'[Fees Billed])-sum(Forecast[forecast]),allselected('Fee Earners'[NAME]))

 

This is ok for when I look at the whole business, but when I filter down to an individual NAME I get their fees billed are compared to the whole forecast, not just theirs.

 

BTW - Budget table has NAMEs that have no Fees Billed and vice versa the Fees Billed Table contains people who have billed but have no budget.

 

QUestions.

 

1) How do I get it so that the result in the visial is just their variance?

 

Thanks

 

Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

     

    Hi Tubster66

     

    Possibly because you are still using the ALL function in your first function.  Possibly try changing that to be ALLSELECTED ??

     Forecast = summarize(
budget,Budget[Name],Budget[Period],Budget[Year],
"forecast",(calculate(sum(Budget[Budget]),ALLSELECTED (Budget[Period]))/12)).

     

2 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

     

    Hi Tubster66

     

    Possibly because you are still using the ALL function in your first function.  Possibly try changing that to be ALLSELECTED ??

     Forecast = summarize(
budget,Budget[Name],Budget[Period],Budget[Year],
"forecast",(calculate(sum(Budget[Budget]),ALLSELECTED (Budget[Period]))/12)).