Group by Year and Staus

[Anonymous]

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:

Name	Amount	Date	Status
james	2000	1/10/2020	Approved
james	100	10/11/2020	rejected
james	1200	8/12/2020	Approved
james	5000	20/03/2021	Approved
james	1500	13/04/2021	Approved
Ajay	2000	1/10/2020	Approved
Ajay	2100	10/11/2020	rejected
Ajay	2500	8/12/2020	Approved
Ajay	15000	20/03/2021	Approved
Ajay	2500	13/04/2021	Approved
Ajay	1500	15/04/2021	rejected

Table2:

Name	Year	Approved Amount
James	2020	3200
James	2021	6500
Ajay	2020	4500
Ajay	2021	17500

[TomMartens · Accepted solution]

Hey @Anonymous ,

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:

Hopefully, this provides what you are looking for.

Regards,

Tom

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

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:

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.

[TomMartens]

Hey @Anonymous ,

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

[TomMartens · Accepted solution]

Hey @Anonymous ,

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:

Hopefully, this provides what you are looking for.

Regards,

Tom

[Anonymous]

Hi Tom

thanks for reply.

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