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 fine gentlemen of the web,
Currently I have the following scenario. I have 2 matrix in a report page, where the rows are the departments and for columns I have the 12 months of the year. One matrix is from 2020 and the other is from 2021. Im trying to show a third matrix with calculations of variance and the monetary difference as well. Any jelp would be appreciated.
Solved! Go to Solution.
@Shackwell in this case, you simply need to create few measures like below
also since for calc variance we would need date as well so i have created date basis month as well like first day of each month
i am also attaching pbix file for your reference
i hope it help you
Proud to be a Super User!
Yes this is great to get me started. I just have a question for one of the strings of the formula. =Calculate(sum(FACTS). What does the Fact operation have to do in here? I think I understood the rest without issues.
if you want sum of values for a year or basis some critria, you use calculate function and second argumnet is the filter criteria like below. facts is basically is the name of the table and value is the column which is being used for sum
2021_Val = calculate(SUM(facts[value]),facts[year]=2021)
if you just need the sum of values from facts table, then you can use simply sum(facts[value])
Proud to be a Super User!
@Shackwell will it be possible for you to share some dummy sample data with the expected output. this will help you in a better way.
Proud to be a Super User!
So here in the sample data you can see the first 2 matrix being filled with departments as rows and months as columns. I want to create a calculation between matrix, having the 3rd matrix doing a substraction between and the 4th matrix to show a percentage change in the given period. Im not sure if you wanted to see the desired output or you wanted to see how is data being added? thanks again,
@Shackwell in this case, you simply need to create few measures like below
also since for calc variance we would need date as well so i have created date basis month as well like first day of each month
i am also attaching pbix file for your reference
i hope it help you
Proud to be a Super User!
Hello there,
We are almost there. I have managed to get the measures going but I have encountered the following. There might be some spaces with 0 number allocation, so whenever Im trying to divide I get that the result is infinite. Also there's a month before January in the report marked as blanks but still shows calculation within the departments.
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |