Forum Discussion
Helper IAlways execute measure on daily resolution
Hello,
PowerBi World, i need your help.
Struggling with the correct calculations while switching between daily and monthly resolution or including more then one month into the report.
Attached is a link to google drive, included is the PowerBi file with the current results and an Excel-Sheet with the raw data and the wanted result. Within excel I can set up a table an a few formulars witch generate the desired results.
PowerBI is challenging, I managed to create a measure which is calculating the Loss during the time of limitation.
But this is only correct, if I filter for one month and the table is on daily resolution.
If I change the report and it does cover more than one month, the measures don't really work.
It's summing up all the needed inputs on monthly totals, but I need the sum of the daily calculation.
If you change the date to monthly basis, the result for the row subtotals are not the wanted result.
Hope the excel file does highlight the way, how I would like to calculate the final values.
I am happy to replay to all of your questions.
https://drive.google.com/drive/folders/1cREWHeYnoXrbzPAhkAfI4LN3pih5ieSB?usp=sharing
Hi,
Please try something like below if it works.
Loss Limitation = SUMX ( SUMMARIZE ( 'RawData Asset', 'Calendar'[Month] ), CALCULATE ( SUM ( 'RawData Asset'[POA Limitation] ) ) * ( [PR excluding Limitation] - [PR Limitation] ) )Hi,
Thank you for the explanation.
Because I do not fully understand the logic of each measure in the Power BI report, the performance of the newly-written-measure might be slow if the real dataset's size is huge, but for now, please check the attached pbix file.
10 Replies
- Jihwan_Kim
Super User
Hi,
Please try something like below if it works.
Loss Limitation = SUMX ( SUMMARIZE ( 'RawData Asset', 'Calendar'[Month] ), CALCULATE ( SUM ( 'RawData Asset'[POA Limitation] ) ) * ( [PR excluding Limitation] - [PR Limitation] ) )
MangnuelHello Jihwan_Kim
thanks, it works.
But it's quite sensitive and I don't really know why.
I added a few lines from a adjusted measure. This does not work.
All I did was, changing the substraction within the SUM(x) by doing it befor within a Variable defintion.
How come this does not work anymore?Loss Limitation = VAR vDiff = [PR excluding Limitation] - [PR Limitation] RETURN Sumx( SUMMARIZE( 'RawData Asset', 'Calendar'[Month]), CALCULATE(sum('RawData Asset'[POA Limitation])) * vDiff
- Jihwan_Kim
Hi,
Thank you for your message, and please check the link down below.
-> Once resultant values have been calculated for a variable expression, those values do not change, even if the variable is referenced in another expression.
- lbendlin
If you want this on daily level you need to calculate the formula separately for each day.
PR Limitation = SUMX(VALUES('Calendar'[Date]),CALCULATE(divide(sum('RawData Asset'[Yield Limitation]),sum('RawData Asset'[POA Limitation]))))Note: No need for multiple RETURN statements in your scenario.
Note: The data model relationship should not be bidirectional.
- Mangnuel
Hi lbendlin
I adjusted my measures, now I have different Measure for the PR Limitation and included it into the loss Calculation.
But I doesn't work,...PR Limitation 2 = sumx( Values('Calendar'[Date]), CALCULATE(DIVIDE(sum('RawData Asset'[Yield Limitation]), sum('RawData Asset'[POA Limitation])) ) )Loss Limitation classic = VAR vDiff = [PR excluding Limitation] - [PR Limitation 2] RETURN vDiff * sum('RawData Asset'[POA Limitation])
- Mangnuel
Hello again,
Hello Jihwan_Kim
I am having problems with this measure.
It is perfectly working, but not if the loss calculation is negative.
Such days are possible, let's over right such result to zero or leave them blank.
Changing the daily result is not a problem, but the monthly result is doing the sum of all daily values (including the negative daily result). Files on the google drive are updated. I did set up an easy example in March, one day with a positive and one day with a negative loss.
Can we somehow adjust the measure, so negative results are not included for daily/monthly or yearly views.
Or do the corrections within a separate measure?
Many thanks for the input, I am happy to answer upcoming questions.
https://drive.google.com/drive/folders/1cREWHeYnoXrbzPAhkAfI4LN3pih5ieSB?usp=drive_link- Jihwan_Kim
Hi, I am not sure if I understood your question, but please show and picture how you want to see the result and that might help to understand your expected outcome correctly.
- Mangnuel
Yes, I'll try Jihwan_Kim .
the files on google drive also show the problem.
I added a few words within the report.
And here is a picture of the current result.
Positive and Negative results are ending up in the monthly sum.
The result is 2.13 instead of 2.53 for march.
Hope that helps? Raphaelcurrently using this measure to calculate the loss,
Loss Limitation = VAR vhelp = SUMX ( SUMMARIZE ( 'RawData Asset', 'Calendar'[Month] ), CALCULATE ( SUM ( 'RawData Asset'[POA Limitation] ) ) * ( [PR excluding Limitation] - [PR Limitation] ) ) RETURN vhelp //if(vhelp <= 0 , blank(), vhelp)
if-clause at the end is not giving the wanted result, daily values are ok, but monthly or yearly sums are showing wrong results. In case the result of ( [PR excluding Limitation] - [PR Limitation] ) is negative, this shut not be used for the calculation.
