cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PamLeek
Frequent Visitor

Adding a Filter affecting my tables returned values

Hi

 

I need help in that when I try to apply a BI filter it removes those items from the pivot table and then returns an infinity response.

 

PamLeek_0-1631091187651.png

PamLeek_3-1631091258352.png

 

VOS as a % of Income = SUM('Actual'[P&L Amount]) / CALCULATE(sum('Actual'[P&L Amount]),FILTER(ALLSELECTED('Chart of Accounts'),'Chart of Accounts'[Type] = "Income")) * -100

 

VOS as a % of Budget Income = SUM('Budget'[02-2022R]) / CALCULATE(sum('Budget'[02-2022R]),FILTER(ALLSELECTED('Chart of Accounts'),'Chart of Accounts'[Type] = "Income")) * -100

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @PamLeek ;

In your case, because your dax 's conditional is [Type]="Income" and use allselected(), however you only filter [Type]="Variable Opreating", so CALCULATE(sum('Actual'[P&L Amount]),FILTER(ALLSELECTED('Chart of Accounts'),'Chart of Accounts'[Type] = "Income")) * -100 return 0. Because these two conditions are in conflict.

so i think you could modify the measure :

VOS as a % of Budget Income =
SUM ( 'Budget'[02-2022R] )
    / CALCULATE (
        SUM ( 'Budget'[02-2022R] ),
        FILTER ( ALL ( 'Chart of Accounts' ), 'Chart of Accounts'[Type] = "Income" )
    ) * -100
VOS as a % of Income =
SUM ( 'Actual'[P&L Amount] )
    / CALCULATE (
        SUM ( 'Actual'[P&L Amount] ),
        FILTER ( ALL ( 'Chart of Accounts' ), 'Chart of Accounts'[Type] = "Income" )
    ) * -100

vyalanwumsft_0-1631496668756.png

Looking forword to your reply!

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yalanwu-msft
Community Support
Community Support

Hi, @PamLeek ;

In your case, because your dax 's conditional is [Type]="Income" and use allselected(), however you only filter [Type]="Variable Opreating", so CALCULATE(sum('Actual'[P&L Amount]),FILTER(ALLSELECTED('Chart of Accounts'),'Chart of Accounts'[Type] = "Income")) * -100 return 0. Because these two conditions are in conflict.

so i think you could modify the measure :

VOS as a % of Budget Income =
SUM ( 'Budget'[02-2022R] )
    / CALCULATE (
        SUM ( 'Budget'[02-2022R] ),
        FILTER ( ALL ( 'Chart of Accounts' ), 'Chart of Accounts'[Type] = "Income" )
    ) * -100
VOS as a % of Income =
SUM ( 'Actual'[P&L Amount] )
    / CALCULATE (
        SUM ( 'Actual'[P&L Amount] ),
        FILTER ( ALL ( 'Chart of Accounts' ), 'Chart of Accounts'[Type] = "Income" )
    ) * -100

vyalanwumsft_0-1631496668756.png

Looking forword to your reply!

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

PamLeek
Frequent Visitor

Hi @amitchandak  That doesn't seem to work either. See below I have Income in the table and the percentage (using my old calculation) works

PamLeek_0-1631172088332.png

 

but when i remove the income from the table (filter it out) it dissappears

PamLeek_1-1631172116116.png

 

amitchandak
Super User
Super User

@PamLeek , example 1 or the two ways

 

VOS as a % of Income =
var _1 = SUM('Actual'[P&L Amount])
var _2 = CALCULATE(sum('Actual'[P&L Amount]),FILTER(ALLSELECTED('Chart of Accounts'),'Chart of Accounts'[Type] = "Income"))
return
divide(_1,_2)

 

or

 

VOS as a % of Income =
var _1 = SUM('Actual'[P&L Amount])
var _2 = CALCULATE(sum('Actual'[P&L Amount]),FILTER(ALLSELECTED('Chart of Accounts'),'Chart of Accounts'[Type] = "Income"))
return
if(isblank(_2) , divide(_1,_2) )

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors