How to usw the last entry of a series as a total denominator?

[NovaBI]

Hi everyone,

i have values that are accumulate orders per workday. The workday data is from the SAP-connector, so no need/chance to recreate this now to a time intelligence format.

So let's say a table looks like this:

Workday   Value

1               1

2               10

3               11

4               25

5               30

Now i want to calculate the achieved % per day, i.e.

Day 1: 1/30

Day 2: 10/30

and so on until the last day equals 100%

I want to plot this against a column chart with this year and last years values. But that should not be the problem.

My issue is simple but somehow complex -> how can i use the last entry, i.e. the value 30 as a static denominator in the calculation.

Whenever i want to plot a chart or a table that is showing the workdays, it only shows the 100% for the last day (what makes sense i tried it with Calculate(Sum(....),Filter(...),Filter(...) where the Filter represented the Year and the last workday.

Any hint on that?

[amitchandak]

@NovaBI , Try like

divide(max(table[value]), maxx(allselected(Table),table[Value]))

[AllisonKennedy]

@NovaBI I'm assuming you have oversimplified your sample data.

Do you have a DimDate table?
https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

Please elaborate on how you find last.

Does the Value ever decrease? If not, you could simply use the MAX(table[value]) as the denominator in your DIVIDE function with a filter for ALLSELECTED(DimDate[Year]) for example.

[mahoneypat · Accepted solution]

You can use a measure like this to get your pct of max.

Pct of Max =
VAR maxvalue =
CALCULATE ( MAX ( Table[Value] ), ALL ( Table[Workday] ) )
RETURN
DIVIDE ( MAX ( Table[Value] ), maxvalue )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[NovaBI]

@mahoneypat 

Hi,

can you tell me how i do solve the issue with cummulative values? It means that every Workday is cummulative re the day before.

E.g.:

Workday   Value

1               1

2               3

3               6

I need now the Value 6 as a denumerator, but as the 6 consists of 1,2 and 3 as rows for Workday 3, it will take the 3 as the MAX.

But i need somehow the sum of Workday 3 to be used.

Thanks in advance

[mahoneypat]

The previous suggested measure should not remove any filter on the Year column.  Can you explain how the table with the Year column relates to the table with the Workday column in it, so a modified measure can be provided?

Also, for your last question, do you mean you wan the change between the last day and the previous day (6 -3)?

Regards,

Pat

[NovaBI]

@mahoneypat 

@amitchandak 

@AllisonKennedy 

Hi,

I think I am pretty much there, but one thing is left that I can not resolve by myself:

I am using this now for the calculation of the Total_Sales in the Last Year (i got an SAP extract that is calculation the Sales in Last Year automatically in Total, per Region and so on, that is okay). If I want to calculate the daily progression in %, i am only able to create that for the TOTAL SUM of Sales, as the command:

Total_Sales_Month_LY = CALCULATE(SUM('TableXYZ'[Sales_LY]),FILTER(ALL('TABLEXYZ'),'TABLEXYZ'[Workday]='TABLEXYZ'[MAX_WORKDAY]))

is getting me the right amount for an overall SUM, but the FILTER/ALL denies to get the right amount if I select e.g. a specific Region.

It always creates e.g. 10 as Total_Sales_Month_LY globally. The right amount for e.g. America would be 5. But still the 10 is taken in my % calculations.

What i get is like:

Global:

Total_Sales_Month_LY         Sales_LY         Workday   %achieved

50                                          10                  1              20%  

50                                          20                  2              40%

50                                          30                  3              60%

50                                          40                  4              80%

50                                          50                  5              100%

%achieved-calculation = Divide(SUM(TableXYZ[Sales_LY),Total_Sales_Month_LY])

If i change now a filter for a global region to a specific country, the Sales_LY change to the right amount, the Total_Sales_Month_LY reamains unchanged (pretty sure due to the ALL in the command).

So it is like

Total_Sales_Month_LY         Sales_LY         Workday   %achieved

50                                          2                  1                4%  

50                                          4                  2                8%

50                                          6                  3                12%

50                                          8                  4                16%

50                                          10                5                20%

So I need a way to show the total amount of Sales_LY, only on the last workday of the month (what i tried with the MAX_Workday - calculated column in the formula), with the possibility to filter it later in the visual.

[NovaBI]

Hi,

as the first step was successful now, another question popped up:

As I have the accumulated % for the Last Year now right, i do calculate the current year's data based on another jump of point (forecast instead of like in the last year "realized" values). I need to seperate the logics somehow as currently it looks like this in a table (later on i have to create a chart with both years and %):

For the time beeing i set up a filter for 2021 in X, so it does not appear in 2020. But this solution is not preferable when next years come, I want to automize it as much as possible.

Logically, if i take out the fiscal year, i see this, what is wrong as some data from 2021 is put into Y in 2020.

Any hints how to solve that in a more elegant way?

[NovaBI]

Thanks a lot, that worked well!

[lbendlin]

That sounds too easy. Are there any other groupings involved or do you really just have a single column of workdays?  Do you need to do this by month, by department, or some other dimension?