Forum Discussion

siva_powerbi's avatar
siva_powerbi
Icon for Helper IV rankHelper IV
5 years ago

Filter with ALLExcept function working for few rows and getting failed for other rows

I am writing the measure to calculate the sum of columns and show at the end of matrix, Below is formula:

 

Measure 3 =
var summary =
IF(ISINSCOPE('Table'[Account NAME]), CALCULATE(SUM('Table'[Amount]),
FILTER(ALLEXCEPT('Table','Table'[Account NAME]), 'Table'[Period] in {"Dec 2019", "Sep 2020"})),0)
var in_month = SUM('Table'[Amount])
return

IF(MAX('Table'[Period])="Sum",summary,in_month)

 

Formula is working for all rows in matrix visual expect few, Tried to debug and used different combinations with no luck.

 

 

In above image row with red mark is not showing the sum (Expected value 22065.13) in last column but other rows are working fine.

 

Need help whether a bug in my formula or need to use different approach. Struck on this for couple of days.

 

One more observation Income row has value only for Dec 31 that is only for one day in a month but remaining rows have values for more than one day in a month.

 

Any help is appretiated

Need Help
Show and Tell
Tips and Tricks

3 Replies

    • siva_powerbi's avatar
      siva_powerbi
      Icon for Helper IV rankHelper IV
      5 years ago

      Thanks for reply.. Sure will try to get the pbox file.

       

      Yes it will remove all other than account name but its confusing to see same formula working for few account names but not working for few account name and datasource is same for all.

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi siva_powerbi ,

     

    Sorry for that we don’t understand your issue clearly.

    Could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?

    It will be helpful if you can show us the exact expected result based on the tables.

    Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.