Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
NovaBI
Helper V
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
mahoneypat
Microsoft Employee
Microsoft 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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

9 REPLIES 9
amitchandak
Super User
Super User

@NovaBI , Try like

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

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

Please @mention me in your reply if you want a response.

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.
If you found this post helpful, please give Kudos C

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

mahoneypat
Microsoft Employee
Microsoft 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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@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

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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

 

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 %):

 

NovaBI_0-1597762636959.png

 

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.

 

NovaBI_1-1597762727133.png

 

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

 

Thanks a lot, that worked well!

lbendlin
Super User
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?

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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