Power BI Query - Creating Measure on Measure Column to get Running Total

[Prakash M]

Greetings!

I've searching and struggling for creating a measure column based on other measure columns.

For your reference I've attached both the PBI and Excel.

I was able to achieve the below :

1. Created 2 different Measure to get count of Negative and Positive, as per year and month (Positive and Negative count column in PBI report)
2. Created 2nd Measure to get sum of only Negative option
3. Created a 3rd Measure named as Average and added below formula :

Average = DIVIDE([Spend / Month],[Positive and Negative Count])

In Excel - into 7-2 and 7-3 tabs, I'm looking for the same values (column G and H) which should get updated to Power BI report in table.
I looked for various searches but could not get the desired output.
Column F in both the tabs, are the manual values that we update start of every year through last month.

Let me know if more explanation is required.

Thanks,
Prakash

 

[Prakash M]

Was not able to upload Power BI dashboard report, hence attaching a snap shot.

 

[vletm]

Prakash M - I'm looking for the same values (column G and H)
If Your C-column values are different ... then it would be a challenge ... or how?
7-2 -sheet has 'Total Count'-values and
7-3 -sheet has 'SPEND Positive'-values ( ref to below table)

 

[Chihiro]

In DAX cumulative total generally use following pattern.

Cumulative Value :=
CALCULATE (
    SUM ( [Column] ),
    FILTER (
        ALL ( 'Date'[Date] ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

Where ALL() is used to remove filter context and FILTER function is used to re-filter based on current evaluation context.

Note: This should be in PowerPivot forum as you are asking about DAX measures. I've moved the thread.

EDIT: Oh, I forgot that SUM() only accepts column as argument. Updated to reflect that.

 

[Chihiro]

Looking at your data and how you want to report. I'd recommend that you set up Calendar table (Date Dimension table).

M-Query code for Calendar:

let
    Source = List.Dates(#date(2018,1,1),Duration.Days(#date(2020,1,1)-#date(2018,1,1)),#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "MonthNum", each Date.Month([Date])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "YearMonth", each [Year]*100+[MonthNum]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "MonthName", each Text.Start(Date.MonthName([Date]),3)),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom3",{{"Date", type date}, {"Year", Int64.Type}, {"MonthNum", Int64.Type}, {"YearMonth", Int64.Type}, {"MonthName", type text}})
in
    #"Changed Type"

Not 100% sure what you are looking to do... but try...

MonthIndex = FIRSTNONBLANK('Calendar'[MonthNum],1)

TotalCount = CALCULATE(COUNTA(Data[SPEND REF]),Filter(Data,[DATE]<>BLANK()))

Spend = CALCULATE(SUM(Data[D-SPEND]),FILTER(Data,[DATE]<>BLANK()&&[SPEND REF]="Positive"))/10^6

Avg = DIVIDE([Spend],[TotalCount],0)

Target = IF([Spend]>0,0.25,BLANK())

Avg Running Total =
VAR MaxYM =
    MAX ( 'Calendar'[YearMonth] )
RETURN
    SUMX (
        FILTER (
            SUMMARIZE (
                ALLSELECTED ( Data ),
                'Calendar'[YearMonth],
                "Avg_Val", [Avg],
                "Spend_Val", [Spend]
            ),
            [YearMonth] <= MaxYM
                && [Spend_Val] <> BLANK ()
        ),
        [Avg_Val]
    )

CumulativeTarget = DIVIDE([Avg Running Total],[MonthIndex],0)

Then using Matrix visual, add [YearMonth] as label and all measures except for [MonthIndex] into values.

Result:


NOTE: I haven't fully optimized DAX, but should do what you are looking for.

EDIT: Attached sample workbook.