RE: Calculate and IF? in Sum (subtotal) for each distinct row

[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

[amitchandak]

@Anonymous , what is the source data for that.

Can you share sample data in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

In addition, may I know what can I use to compare last month data to current month?

I cant use the suggested functions because the DAX cannot accept the MMMM-YYYY format. 

The next close idea i can think is to have columns like this:

MONTH                 ---        Amount           ----  Amount Last Month -----

(mmmm-yyyy)

1. SEPTEMBER 2019   --- 5000       ---- 0000

2. OCTOBER 2019 ---- 10000   ----- 5000

3. NOVEMBER 2019 ---- 300     ----   10000

Am I correct? TIA 

[Anonymous]

Hi. Sorry if I wasnt able to include the original data or the generated data. 

From this table:

To this outcome:

Hope this can help. TIA 🙂 @amitchandak 

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Transform data in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc49DoAgDAXguzAzlF9x9AxOhjCY6GCiLt4/scAbJIahQHgfLTEKJWSphcsR8Tpt13Hzbh2JJKPQED+A3PBRo4MpYF7P/cmvQhU2JxC2ERo9HEQX+B5QpoIhJ5ihqPmnp0oCSF+MXWEhVL50mOO/gvP0Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [row = _t, #"Doc Code" = _t, #"Doc Code Sign" = _t, #"Orig Amount" = _t, Department = _t, #"Audited Amt" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"row", Int64.Type}, {"Doc Code", Int64.Type}, {"Doc Code Sign", type text}, {"Orig Amount", Int64.Type}, {"Department", type text}, {"Audited Amt", Int64.Type}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type", {"Doc Code", "Audited Amt"}),
    #"Grouped Rows" = Table.Group(#"Removed Duplicates", {"Doc Code"}, {{"sum", each List.Sum([Audited Amt]), type number}, {"all", each _, type table [row=number, Doc Code=number, Doc Code Sign=text, Orig Amount=number, Department=text, Audited Amt=number]}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"row", "Doc Code Sign", "Orig Amount", "Department"}, {"all.row", "all.Doc Code Sign", "all.Orig Amount", "all.Department"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded all", each ([all.Doc Code Sign] = "Y"))
in
    #"Filtered Rows"

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.