Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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?
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@NovaBI , Try like
divide(max(table[value]), maxx(allselected(Table),table[Value]))
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
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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:
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?
Thanks a lot, that worked well!
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?
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
102 | |
99 | |
38 | |
37 |
User | Count |
---|---|
158 | |
125 | |
76 | |
74 | |
63 |