Sum of values based on distinct rows

[Anonymous]

Hi,

Good day!

I would like to seek for your help on this. what function should I use for the table below. I cant explain it thru words but here's what I need to do for my report and the data table as basis:

Generated data table with 10 Rows

Expected outcome:

i dont know if this gonna be possible in Power BI but I hope you'll help me on this.

PS: Disregard why the issue has been gone.

thanks and godbless

[technolog · Accepted solution]

For this, you can use DAX to create a new table in Power BI.

First, you'll need to create a summarized table that groups by "Doc Code" and sums up the "Audited Amt" for each "Doc Code".

SummarizedTable =
SUMMARIZE(
YourTableName,
[Doc Code],
"Total Audited Amount", SUM(YourTableName[Audited Amt])
)
Next, you'll need to merge this summarized table with your original table to get the "Orig Amount" where "Doc Code Sign" is 'Y'.

MergedTable =
MERGE(
FILTER(YourTableName, YourTableName[Doc Code Sign] = "Y"),
SummarizedTable,
[Doc Code], [Doc Code],
"Merged"
)
Finally, you'll create a calculated column in this merged table to get the final audited amount for each "Doc Code". This will be the "Orig Amount" minus the "Total Audited Amount".

MergedTable[Final Audited Amount] = MergedTable[Orig Amount] - MergedTable[Total Audited Amount]
This will give you the desired table with the "Doc Code", "Doc Code Sign", "Orig Amount", "Departme", and the "Final Audited Amount".