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.
I have this matrix with measure Tot Gross = sum(INVEST[Gross Bal]) . How do I calculate the percentual difference of Tot Gross (being calendar non contiguous due to holidays and weekends)?
Solved! Go to Solution.
Hi @Fcoatis,
You can use a measure, please review the following solution.
I have sample data table.
Add index column in Edit Query-> Close & Apply. You will get the following table.
Create a calculated column using the formula.
Last = LOOKUPVALUE(Table9[value],Table9[Index.1],Table9[Index.1]-1)
Finally, create a measure. For unique date, sum(Table9[value]) equals itself. Create a table visual used to display the result.
Percentage1 = (SUM(Table9[value])-SUM(Table9[Last]))/SUM(Table9[Last])
If you have other issues, please let me know.
Best Regards,
Angelia
Hi @Fcoatis,
What's the mean of the percentual difference of Tot Gross, could you please post an example?
The difference includes increase and decrease between the two numbers you are comparing.
Increase = (New Number - Original Number)÷ Original Number × 100.
Decrease = (Original Number - New Number)÷ Original Number × 100.
In your requirement, what's the New Number and Original Number?
Best Regards,
Angelia
Hi Angelina,
I'm looking for a measure that calculates (actual value/previous value)-1
Hi @Fcoatis,
You can use a measure, please review the following solution.
I have sample data table.
Add index column in Edit Query-> Close & Apply. You will get the following table.
Create a calculated column using the formula.
Last = LOOKUPVALUE(Table9[value],Table9[Index.1],Table9[Index.1]-1)
Finally, create a measure. For unique date, sum(Table9[value]) equals itself. Create a table visual used to display the result.
Percentage1 = (SUM(Table9[value])-SUM(Table9[Last]))/SUM(Table9[Last])
If you have other issues, please let me know.
Best Regards,
Angelia
Thank you Angelia,
I think this is the solution. I'll have to adapt to my model in order to have the right sequential index.
Best regards
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |