Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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.

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

Hi Tom

 

thanks for reply.

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.