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

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.