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 need help
I have three different tables having common attributes and now i want to create measure summing up all three values from three tables. Below is sample example
Examples
Table - Germany Table - France Table - Denmakr
Attribute 1 Attribute 2 Attrubute 1 Attrubute 2 Attribute 1 Attribute2 New Measure
Budget Actual Budget Actual Budget Actaul Total Acutal
500 400 300 300 200 150 850
Good evening,
See if this works for you. I've assumed your source resembles this (if the source data has a different structure, please provide an example):
I imported this into PBI, did some transformations in Power Query, involving splitting the tables into two (budget and actuals) and unpivotting. The resulting model looks like this:
And finally built this matrix:
Here is a link to the file if you're interested: PBIX file
Proud to be a Super User!
Paul on Linkedin.
Hi Sara
Thank you for your reply.
Basically i have three matrix i.e, Sale 1 2 and 3 with attributes Budget and Actual
Now I want to make total of both atrubutes separately as shown in below table
I tried measure with sum but it totals both attribute together though i want budget and actual sepeately
Hope I made little more clearer
Sales 1 | Sales 2 | Sales 3 | Total | ||||||
Budget | Actual | Budget | Actual | Budget | Actual | Budget | Actual | ||
100 | 70 | 200 | 250 | 50 | 40 | 350 | 360 |
Hi @GGDAC ,
Thank you for the details you provided. Computing totals by attribute (Budget, Actual) is automatically done by PowerBI if your data is put together in one table. Please have a look at the snapshot below:
Do you want to have the result in the exact layout your provided in your last post? if yes, could you please provide more details about your model?
Thank you
Hi Sara
My model is like this. I have three markets sales details in matrix and not table
Budget Vs Actual by markets by products
Item | Germany Budget | Germany Actual | Fraance Budget | France Actual | Denmak Budget | Denmark Actuals | Total Budget | Total Actual |
Shoes | 100 | 70 | 150 | 120 | 200 | 250 | 450 | 440 |
Electronics | 50 | 60 | 70 | 70 | 100 | 80 | 220 | 210 |
Total | 150 | 130 | 220 | 190 | 300 | 330 | 670 | 650 |
Total Actual = CALCULATE(SUM(Denmark[Actual])) + CALCULATE(SUM(France[Actual]))+ CALCULATE(SUM(Germany[Actual]))
Total Budget = CALCULATE(SUM(Denmark[Budget])) + CALCULATE(SUM(France[Budget]))+ CALCULATE(SUM(Germany[Budget]))
Paul
Dear Paul
Thank you for reply and solution
I tried the measure but it is not working. It gives me error that "Coumn "Actual" in the table can not be found or in may not be used in this expression"
Also i saw you have attached the file but your report is just with three matrix with budget and actuals. The main is creating the measure which is not there in your PBI report though you have give the formual / measure but you have not used in your report
Your report is simply three matrix but not total of all three actuals
Kind regards
Ali
@GGDAC
Sorry for that, it seems something went wrong that my measures were not show up. I put the measures in the Denmark table.
https://qiuyunus-my.sharepoint.com/:u:/g/personal/paul_qiuyunus_onmicrosoft_com/EesC7JUQ8oNMkXnLUznm...
The error can be because the table relationship in my sample are different to yours. If you want the absolute solution, I think you could provide a sample pbix.
Best,
Paul
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 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |