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
Hi All,
I need to build something like the following:
last row and last column are calculated using other rows and columns.
Is the following something possible to build in powerbi?
Please advise.
Solved! Go to Solution.
Hello, thats a question of what you need to see the the matrix. is it two columns? thats a bit easier since its the difference between the 2. Is it 12 months? if so what difference do you want to see. The variance will be written into measure calculation. The syntax will be something like:
Income =
VAR Income=SUM( Table1, Income)
VAR IncomeFirstMonth = CALCULATE(Income,FIRSTNONBLANK(MonthDate))
VAR IncomeLastMonth= CALCULATE(Income,LASTNONBLANK(MonthDate))
RETURN
IF(HASONEVALUE(Date[Date]),Income,IncomeFirstMonth-IncomeLastMonth))
Have a look here for a really helpful collection of total calcs which you should be able to rewrite to get what you need: https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Hi,
This depends upon the structure of your data. Share the link from where i can download your PBI file.
Hello, yes thats quite achieveable in a matrix.
1. Drag months on the column field then the three measures in the values section.
2. Alter the formatting for values to show vales on rows once you have added them.
the Measures will be some thing like
Income = SUM( Table1, Income)
No of Cases = DISTINCTCOUNT( Table1, CaseID)
Incopme /No of Cases= [Income]/[No of Cases]
hi Thanks for the reply.
How about the variance column?
Hello, thats a question of what you need to see the the matrix. is it two columns? thats a bit easier since its the difference between the 2. Is it 12 months? if so what difference do you want to see. The variance will be written into measure calculation. The syntax will be something like:
Income =
VAR Income=SUM( Table1, Income)
VAR IncomeFirstMonth = CALCULATE(Income,FIRSTNONBLANK(MonthDate))
VAR IncomeLastMonth= CALCULATE(Income,LASTNONBLANK(MonthDate))
RETURN
IF(HASONEVALUE(Date[Date]),Income,IncomeFirstMonth-IncomeLastMonth))
Have a look here for a really helpful collection of total calcs which you should be able to rewrite to get what you need: https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
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 |
---|---|
100 | |
94 | |
81 | |
63 | |
56 |
User | Count |
---|---|
247 | |
122 | |
110 | |
77 | |
72 |