The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
97 | |
80 | |
62 | |
57 |
User | Count |
---|---|
262 | |
120 | |
115 | |
83 | |
70 |