Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I have this issue where I cannot find the related subtotals.
I have a datasource with these three tables, with simplified contents here:
Project-Sales
- Project-ID
- Invoice-value
Project-Member
- Project-ID
- Person-ID
Persons
- Person-ID
- Name
I have set up relations
Project-Member to Project-Sales: Many to One
Project-Member to Persons: Many to One
Now I want to summarize the sales subtotal for each person, but summarizing like
CALCULATE(SUM(Project-Sales[Invoice-Value]))
Only gives me the Total of all projects. How can I make the summary only use the subtotals for each person?
Solved! Go to Solution.
As per your logic, I think it is simple to solve your problem per below measure,
Hi @JoaPalmer ,
You can try this method:
Sample data:
In Project-Member table, new a measure:
Count = CALCULATE(COUNT('Project-Member'[Project-ID]), ALLEXCEPT('Project-Member','Project-Member'[Project-ID]))
Then new a table:
Average = SUMMARIZE('Project-Member','Project-Member'[Project-ID],'Project-Sales'[Invoice-value], "Count", [Count])
In Project-Member table, new a column:
Per = CALCULATE(SUM('Average'[AVG]), FILTER('Average','Average'[Project-ID] = 'Project-Member'[Project-ID]))
New a measure:
Rusult = CALCULATE(SUM('Project-Member'[Per]), FILTER('Project-Member','Project-Member'[Person-ID]))
The result is:
Hope this helps you.
Here is my PBIX file.
Best Regards,
Community Support Team _Yinliw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank You for your extensive reply. However the result calculation you suggest is not correct.
In Your example, person 1001 is a member of project ID 1, 3 and 4. Sum of sales for those project is 10+30+40=80, not 60 as your calculation shows.
I don't know if there is an misunderstanding. I just want to get the summaries for the projects for each person involved, I don't understand why I would want to count rows or make average calculations?
Please I'm happy for your continued help! 🙂
As per your logic, I think it is simple to solve your problem per below measure,
This was so much easier solution, thank You very much, works perfect! 🙂
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
95 | |
90 | |
35 | |
35 |
User | Count |
---|---|
154 | |
102 | |
82 | |
64 | |
54 |