cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper V

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

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?

1 ACCEPTED SOLUTION
Employee

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

9 REPLIES 9
Super User

@NovaBI , Try like

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

Super User
@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.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Employee

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Helper V

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.

Employee

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Helper V

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.

Helper V

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?

Helper V

Thanks a lot, that worked well!

Super User

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?

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors