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'm hoping someone can point me in the right direction, because I'm starting to think what I'm trying to do can't be done.
Data Model:
Server Detail (InstanceID) 1-to-many to BillingDetail (InstanceID, ExtendedCost)
Standard Date table
I split ServerDetail into SingleServers and SharedServers based on certain criteria (there is no overlap between SingleServers and SharedServers, and every row from ServerDetail goes into one or the other).
So the data model really looks like this:
I need to total up the costs, per month of the SingleServers, and figure out the percentage of each for a given month:
Total Cost := CALCULATE( sum(BillingDetail[ExtendedCost]), 'SingleServers' ) %GT := DIVIDE( [Total Cost], CALCULATE( [Total Cost], ALLSELECTED(SingleServers[Dept]) ) )
Solved! Go to Solution.
Somehow I made 2 or 3 copies of this thread, but the main one is here:
I will be sharing PBIX files with both split and non-split tables showing that both methods can work.
Thanks,
David
I'm hoping someone can point me in the right direction, because I'm starting to think what I'm trying to do can't be done.
Data Model:
Server Detail (InstanceID) 1-to-many to BillingDetail (InstanceID, ExtendedCost)
Standard Date table
I split ServerDetail into SingleServers and SharedServers based on certain criteria (there is no overlap between SingleServers and SharedServers, and every row from ServerDetail goes into one or the other).
So the data model really looks like this:
I need to total up the costs, per month of the SingleServers, and figure out the percentage of each for a given month:
Total Cost := CALCULATE( sum(BillingDetail[ExtendedCost]), 'SingleServers' ) %GT := DIVIDE( [Total Cost], CALCULATE( [Total Cost], ALLSELECTED(SingleServers[Dept]) ) )
I'm hoping someone can point me in the right direction, because I'm starting to think what I'm trying to do can't be done.
Data Model:
Server Detail (InstanceID) 1-to-many to BillingDetail (InstanceID, ExtendedCost)
Standard Date table
I split ServerDetail into SingleServers and SharedServers based on certain criteria (there is no overlap between SingleServers and SharedServers, and every row from ServerDetail goes into one or the other).
So the data model really looks like this:
I need to total up the costs, per month and department, of the SingleServers, and figure out the percentage of each for a given month:
Total Cost := CALCULATE( sum(BillingDetail[ExtendedCost]), 'SingleServers' ) %GT := DIVIDE( [Total Cost], CALCULATE( [Total Cost], ALL(SingleServers[Dept]) ) )
So for example I would get
In this scenario, since you separate the server detail into two tables, you can't use a single measure to dynamically retrieve the data from two tables and calculate the percentage. The better practice is adding a column in server detail table for tagging single or shared, then you can use ALLEXCEPT() to group your calculation based on this tagging column.
Regards,
Somehow I made 2 or 3 copies of this thread, but the main one is here:
I will be sharing PBIX files with both split and non-split tables showing that both methods can work.
Thanks,
David
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |