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.
Hello Experts,
Need your expertise help on power bi - Excel ( cumulative for months calculations). Tried all ways, but not able to get for all divisions.
Please help me
I have uploaded excel file. need your help.
Column A to Column E are inputs for power bi
need output like mentioned in excel
Three formulas need to implement to get final output.
Any help is highly appreciated.
Thanks,
Rajendra
Solved! Go to Solution.
1. Yes
2. your previous sample data didn't need protection against division by zero. Change the formula to
Result =
var m = max('Source File'[Month Number])
var dc1 = CALCULATE(sum('Source File'[DistCosts]),'Source File'[Year]="2021", 'Source File'[Month Number] = m)
var dc0 = CALCULATE(sum('Source File'[DistCosts]),'Source File'[Year]="2020", 'Source File'[Month Number] = m)
var is0 = CALCULATE(sum('Source File'[Invoice Sales]),'Source File'[Year]="2020", 'Source File'[Month Number] = m)
var is1 = CALCULATE(sum('Source File'[Invoice Sales]),'Source File'[Year]="2021", 'Source File'[Month Number] = m)
var dc0p = CALCULATE(sum('Source File'[DistCosts]),ALLEXCEPT('Source File','Source File'[Division],'Source File'[Region]),'Source File'[Year]="2020",'Source File'[Month Number] <m)
var is0p =CALCULATE(sum('Source File'[Invoice Sales]),ALLEXCEPT('Source File','Source File'[Division],'Source File'[Region]),'Source File'[Year]="2020",'Source File'[Month Number] <m)
var is1p = CALCULATE(sum('Source File'[Invoice Sales]),ALLEXCEPT('Source File','Source File'[Division],'Source File'[Region]),'Source File'[Year]="2021",'Source File'[Month Number] <m)
return (divide(dc0p+dc0,is0p+is0,0)*(is1p+is1)-dc1-divide(dc0p,is0p,0)*is1p)/1000
Hi @lbendlin ,
Thanks a lot for your help. I am almost near by. having below issues. Your inputs might help me.
1. I forgot to mention, there is region field, where I need to put in filter and I need to exclude two regions. Shall I write "Region" field beside division in formula.
2. When I drag month (jan) or monthnumber (1) into my table, below error throwing. Please help.
"calculation error in measure (result); the result of a conversion or arithmetic operation is either too large or too small"
1. Yes
2. your previous sample data didn't need protection against division by zero. Change the formula to
Result =
var m = max('Source File'[Month Number])
var dc1 = CALCULATE(sum('Source File'[DistCosts]),'Source File'[Year]="2021", 'Source File'[Month Number] = m)
var dc0 = CALCULATE(sum('Source File'[DistCosts]),'Source File'[Year]="2020", 'Source File'[Month Number] = m)
var is0 = CALCULATE(sum('Source File'[Invoice Sales]),'Source File'[Year]="2020", 'Source File'[Month Number] = m)
var is1 = CALCULATE(sum('Source File'[Invoice Sales]),'Source File'[Year]="2021", 'Source File'[Month Number] = m)
var dc0p = CALCULATE(sum('Source File'[DistCosts]),ALLEXCEPT('Source File','Source File'[Division],'Source File'[Region]),'Source File'[Year]="2020",'Source File'[Month Number] <m)
var is0p =CALCULATE(sum('Source File'[Invoice Sales]),ALLEXCEPT('Source File','Source File'[Division],'Source File'[Region]),'Source File'[Year]="2020",'Source File'[Month Number] <m)
var is1p = CALCULATE(sum('Source File'[Invoice Sales]),ALLEXCEPT('Source File','Source File'[Division],'Source File'[Region]),'Source File'[Year]="2021",'Source File'[Month Number] <m)
return (divide(dc0p+dc0,is0p+is0,0)*(is1p+is1)-dc1-divide(dc0p,is0p,0)*is1p)/1000
Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.
Thanks for your time.
here is my PBI file and source file.
PBI - https://www.dropbox.com/s/kpjbz9vvd50znhj/source.pbix?dl=0
Hi,
Clearly show how you arrived at the numbers on the Result worksheet.
Slightly more efficient version:
Result =
var m = max('Table'[Date])
var dc1 = CALCULATE(sum('Table'[DC]),'Table'[year]="2021",'Table'[Date]=m)
var dc0 = CALCULATE(sum('Table'[DC]),'Table'[year]="2020",'Table'[Date]=m)
var is0 = CALCULATE(sum('Table'[IS]),'Table'[year]="2020",'Table'[Date]=m)
var is1 = CALCULATE(sum('Table'[IS]),'Table'[year]="2021",'Table'[Date]=m)
var dc0p = CALCULATE(sum('Table'[DC]),ALLEXCEPT('Table','Table'[Division]),'Table'[year]="2020",'Table'[Date]<m)
var is0p = CALCULATE(sum('Table'[IS]),ALLEXCEPT('Table','Table'[Division]),'Table'[year]="2020",'Table'[Date]<m)
var is1p = CALCULATE(sum('Table'[IS]),ALLEXCEPT('Table','Table'[Division]),'Table'[year]="2021",'Table'[Date]<m)
return ((dc0p+dc0)/(is0p+is0)*(is1p+is1)-dc1-dc0p/is0p*is1p)/1000
Thank you @lbendlin .
You are awesome. Let me try in my dashboard and get back to you.
Thanks again for your wonderful formula.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
95 | |
83 | |
70 | |
66 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |