cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ajay_bi_help
New Member

Group by Year and Staus

Hi Team,

 

I am new to power bi and need help on below requirement.please check and help

 

I have below Data set and i need the total approved amount of employee per year to be displayed in a separate table (Table 2) and the same details should be displayed on select of a row from Table 1. (Ex if FIrst 5 rows are selected where name is James, The Table 2 should show only James Approved amount details)

Table1:

NameAmountDateStatus
james20001/10/2020Approved
james10010/11/2020rejected
james12008/12/2020Approved
james500020/03/2021Approved
james150013/04/2021Approved
Ajay20001/10/2020Approved
Ajay210010/11/2020rejected
Ajay25008/12/2020Approved
Ajay1500020/03/2021Approved
Ajay250013/04/2021Approved
Ajay150015/04/2021rejected

 

Table2:

NameYearApproved Amount
James20203200
James20216500
Ajay20204500
Ajay202117500
2 ACCEPTED SOLUTIONS
TomMartens
Super User
Super User

Hey @Ajay_bi_help ,

 

I'm not 100% sure if I understand your requirement correctly, but nevertheless, this DAX measure:

Measure = 
calculate(
    sum( 'Table'[Amount] )
    ,  KEEPFILTERS( 'Table'[Status] = "Approved" )
)

Allows to create this table visual:

D8D300CE-77C9-4243-8CA9-9C9F1672BC06.jpeg

Hopefully, this provides what you are looking for.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

v-yalanwu-msft
Community Support
Community Support

Hi, @Ajay_bi_help ;

You also could create a table2 by dax.

Table2 = SUMMARIZE(FILTER('Table',[Status]="Approved"),[Name],[Date].[Year],"Approved Amount",SUM('Table'[Amount]))

The final output is shown below:

vyalanwumsft_0-1629771976503.png

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

4 REPLIES 4
v-yalanwu-msft
Community Support
Community Support

Hi, @Ajay_bi_help ;

You also could create a table2 by dax.

Table2 = SUMMARIZE(FILTER('Table',[Status]="Approved"),[Name],[Date].[Year],"Approved Amount",SUM('Table'[Amount]))

The final output is shown below:

vyalanwumsft_0-1629771976503.png

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

Hey @Ajay_bi_help ,

if you are using SUMMARIZE to create a table, instead of using a measure in combination with your existing measure, you should also be aware of this article that describes some best practice using SUMMARIZE: https://www.sqlbi.com/articles/best-practices-using-summarize-and-addcolumns/

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
TomMartens
Super User
Super User

Hey @Ajay_bi_help ,

 

I'm not 100% sure if I understand your requirement correctly, but nevertheless, this DAX measure:

Measure = 
calculate(
    sum( 'Table'[Amount] )
    ,  KEEPFILTERS( 'Table'[Status] = "Approved" )
)

Allows to create this table visual:

D8D300CE-77C9-4243-8CA9-9C9F1672BC06.jpeg

Hopefully, this provides what you are looking for.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

Hi Tom

 

thanks for reply.

 

this is what i wanted . i will implement and get back to u in a while

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.