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

Filter All not working when field is not in the table

Hi Everyone, 

i have a problem with my query, could not find the solution for it. I appreciate if you can assist.

below is a screen shot of my report :

 

 
 

Report Screen shot.jpg

 

below is some explanation of the report

in table 3, i am calculating cumulative ceded premium based on the below formula:

 

 

 

Cumulative Ceded Premium = CALCULATE (      
   [Our Share of GCP],      
   FILTER (      
      ALL ( LineReport[Index Year] ),      
      LineReport[Index Year] <= MAX ( LineReport[Index Year])  
   )    
)

 

 

Index Year is in columns (1,2,3,4,5,6,7,8)

 

the above is working fine.

 

My problem is in table 1, where i should retreive the cumulative amount for a specific year (without putting the year in Rows)

for example:  in Table 1 (look only first row Premium, 3 years) Coumn Index Year 7, i should get 125,816,623 (Table 3 , Column Index Year 7, Row UW Year 2014 which is the max UW year for index year 7), while im getting 143,406,566 which is the amount showing in Column index 6 , UW Year 2014 in Table 1). it is always taking the info from previous year.

 

note that if i put the field UW Year the amounts are displayed properly: check table 2 Index Year 7, UW Year 2014, the amount is 125,816,623.

 

i tried many formulas using Filter All functions but none of them worked to give me the correct amount:

 

 

Premium 3 Years = 
CALCULATE (     
   [Our Share of GCP],      
   FILTER (      
      ALL( LineReport[Index Year],LineReport[UW Year] ),      
      AND(LineReport[Index Year] <= MAX ( LineReport[Index Year]), LineReport[UW Year]=MAX(LineReport[UW Year])
   )    
)  
   
)

 

 

 

also i tried:

 

 

Premium 3 Years = 
CALCULATE (     
   [Cumulative Ceded Premium],      
   FILTER (      
      ALL( LineReport[UW Year] ),      
      LineReport[UW Year]=MAX(LineReport[UW Year])
   )    
)  
   

 

 

 even i put single value 2014 instead of Max(LineReport[UW Year]), i get same results.

 

appreciate your assistance the soonest possible.

 

Thank you in advance.

 

Best regards

Georges Sabbagh

3 REPLIES 3
Highlighted
Resident Rockstar
Resident Rockstar

Re: Filter All not working when field is not in the table

Try using DATEADD here and you will need to have some context of YEAR on that table visualization in order to tell Power BI what year is the starting point. DATEADD can then be used to count backwards 3, 7, etc years.

 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


https://sites.google.com/site/allisonkennedycv

Highlighted
Anonymous
Not applicable

Re: Filter All not working when field is not in the table

Without knowing the structure of the model it's not possible to answer this.

Best
D
Highlighted
Frequent Visitor

Re: Filter All not working when field is not in the table

Hi again,

thank you for your feedback, let me try to simplify the problem.

 

below is a sample of data, i need to calculate the cumulative amount by IndexYear.

 

Entity

UWYear

IndexYear

Premium

Cyprus

2014

5

100

Cyprus

2014

5

50

Cyprus

2014

6

200

Cyprus

2014

6

100

Dubai

2014

5

50

Dubai

2014

5

50

Dubai

2014

6

100

Dubai

2014

7

200

SAL

2015

8

500

Cyprus

2013

5

10

Cyprus

2013

5

10

Cyprus

2013

6

10

Cyprus

2013

6

10

Dubai

2013

5

10

Dubai

2013

5

10

Dubai

2013

6

10

Dubai

2013

7

10

SAL

2013

8

10

 

i am using the below formula:

 

Cumulative Premium = CALCULATE ( SUM(Data[Premium]), FILTER ( ALL ( Data[IndexYear] ), Data[IndexYear] <= MAX ( Data[IndexYear] ) ) )

 

the issue is that i need to calculate cumulative by indexyear for each UWYear, even if entity doesnt have a value for a specify indexyear, i need to calculate the premium for the whole UWYear.

 

the results are as per the below:

 

UWYear

5

6

7

8

Grand Total

2014

250

400

200

 

850

2015

 

 

 

500

500

 

while it should be 

 

UWYear

5

6

7

2014

250

650

850

 

below is how Entity is distributed:

 

Entity

5

6

7

8

Total

Cyprus

150

300

 

 

450

Dubai

100

100

200

 

400

SAL

 

 

 

500

500

 

As you can see in index it is not calculating the cumulative properly since in Cyprus I don’t have any rows for index 7.

It is calculating the cumulative for Dubai disregarding the cumulative of Cyprus (as at column 6)

 

The output should calculate all the cumulative amounts less than or equal to 7 (both Cyprus and Dubai cumulative instead of only Dubai)

 

i tried to attached screen shots but didnt work so i pasted only tables.

 

i hope i clarified the problem

 

appreciate your assistance

 

Best regards,

 

 

 

 

 

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors