• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Prakash M

New Member
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
 

Attachments

vletm

Excel Ninja
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)
Screenshot 2020-01-07 at 14.20.39.png
 

Chihiro

Excel Ninja
In DAX cumulative total generally use following pattern.
Code:
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.
 
Last edited:

Chihiro

Excel Ninja
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:
Code:
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...
Code:
MonthIndex = FIRSTNONBLANK('Calendar'[MonthNum],1)
Code:
TotalCount = CALCULATE(COUNTA(Data[SPEND REF]),Filter(Data,[DATE]<>BLANK()))
Code:
Spend = CALCULATE(SUM(Data[D-SPEND]),FILTER(Data,[DATE]<>BLANK()&&[SPEND REF]="Positive"))/10^6
Code:
Avg = DIVIDE([Spend],[TotalCount],0)
Code:
Target = IF([Spend]>0,0.25,BLANK())
Code:
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]
    )
Code:
CumulativeTarget = DIVIDE([Avg Running Total],[MonthIndex],0)
Then using Matrix visual, add [YearMonth] as label and all measures except for [MonthIndex] into values.

Result:
64845

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

EDIT: Attached sample workbook.
 

Attachments

Last edited:
Top