Running total of a percentage measure DAX

[Kraftfood]

Hello, I'm not sure if this situation has been answered before, I tried a few solutions from other posts but none worked.

I have a series of sales, dated, and an objective sales for 2022.

I created a measure that calculates the % of completion simply by dividing sales by objectives.

Now I would like the running total % of this completion. I tried to use this DAX formula but no success:

Running total = SUMX(FILTER(ALLSELECTED('TABLE'[DATE]),'TABLE'[DATE]<=MAX('TABLE'[DATE])),[Completion %])

Does anyone encountered the same issue?

Screenshot of the table in Excel, I want the same in BI but cannot get the last column.

[PaulDBrown]

Try:

Running Total % =
VAR _Sales = CALCULATE([Sum Sales], FILTER (ALLSELECTED (Table[Date]), Table[Date]<= MAX(Table[Date]))

VAR _Budget = AVERAGEX(VALUES(Table[Date]), [Sum Budget)

RETURN

DIVIDE(_Sales, _Budget)

[Kraftfood]

Hi thanks for the help. I changed the formula for the Sum Sales and Sum Budget + added a ")" at the end of both VAR, see below:

VAR _Sales = CALCULATE(Sum(Table[Sales]), FILTER (ALLSELECTED (Table[Date]), Table[Date]<= MAX(Table[Date])))

VAR _Budget = AVERAGEX(VALUES(Table[Date]), Sum(Table[Budget]))

RETURN

DIVIDE(_Sales, _Budget)

But I still have the same values as the column completion

[PaulDBrown]

How is your data structured? Do you have a date table? Are sales and budget from different tables?

[Kraftfood]

I have a star schema. I don't have a data table, the date column is from the main table, one line per transactions. Sales is included in the main table, however the budget is from another table.

Let me fix my previous answer:

VAR _Sales = CALCULATE(Sum(Sales[Sales]), FILTER (ALLSELECTED (Sales[Date]), Sales[Date]<= MAX(Sales[Date])))

VAR _Budget = AVERAGEX(VALUES(Table[Date]), Sum(Budget[Budget]))

RETURN

DIVIDE(_Sales, _Budget)

[PaulDBrown]

Let's see if this works (slight variation), though you should ideally be working with a Date Table

% Running Total =
VAR _Sales =
    CALCULATE (
        [Sum Sales],
        FILTER ( ALLSELECTED ( Table ), Table[date] <= MAX ( Table[date] ) )
    )
VAR _Budget =
    AVERAGEX ( VALUES ( Table[date] ), [Budget] )
RETURN
    DIVIDE ( _Sales, _Budget )

Or

Running Total % =
SUMX (
    FILTER ( ALLSELECTED ( Table ), Table[date] <= MAX ( Table[date] ) ),
    CALCULATE ( [Completion] )
)

[Kraftfood]

Tried the first one but, the [Budget] in AVERAGEX has to be a measure.

For the second one I have weird figures...: 

[PaulDBrown]

It would be easier if you provided sample non-confidential data of the tables involved. Otherwise it's guess work

[Kraftfood]

Hi Paul sorry I have been busy.

Here is the link to a situtation similar to mine. 

https://drive.google.com/file/d/1R2db5dbrFQVlPSBjTsARYX9focl7Jxjf/view?usp=sharing

https://1drv.ms/u/s!AkGwzt_UiPRCg21RqEzdVMiJ01hY?e=zaaK15

Let me know if one of the links work, I have issues sharing power bi documents via google drive and 1 drive.

[PaulDBrown · Accepted solution]

@Kraftfood 

Thanks for the file!

Here goes...

Create a Calendar table using:

Calendar Table =
ADDCOLUMNS (
    CALENDAR ( MIN ( Sales[Operation date] ), MAX ( Sales[Operation date] ) ),
    "MonthNum", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "Year", YEAR ( [Date] )
)

Make sure the date fields are formatted as type Date

Create a relationship between the Calendar Table and Sales via the corresponding Date fields. The model looks like this:

Next create the following measures:

Sum Sales K = 
SUM(Sales[Revenue K])

Sum Budget = 
SUM(Budget[Objective])

% Completion Sales k =
VAR _Running =
    CALCULATE (
        [Sum Sales K],
        FILTER (
            ALLSELECTED ( 'Calendar Table'[Date] ),
            'Calendar Table'[Date] <= MAX ( 'Calendar Table'[Date] )
        )
    )
RETURN
    DIVIDE ( _Running, [Sum Budget] )

or if you prefer using SUMX

SUMX completion % =
SUMX (
    FILTER (
        ALLSELECTED ( 'Calendar Table'[Date] ),
        'Calendar Table'[Date] <= MAX ( 'Calendar Table'[Date] )
    ),
    [Completion %]
)

Use the date field from the Calendar Table in the visual to get:

I've attached the sample PBIX file

[Kraftfood]

Do you know why when looking at the figures through a table like that we don't get the right % please?

[PaulDBrown]

You need to use the Category field from the Budget Table:

[Kraftfood]

Thank you!

[Kraftfood]

Thank you very much! Was it just a date issue?