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.
The company is question has two lines of business
The revenue and cost for Accessories are always added to the sales table at the same level of detail so there is no problem there. For Bikes, the revenue is added at a more granular level than the cost and at different times. Revenue is posted more often and includes the customer level where cost is posted twice a month and does not include the customer.
Given that the cost is in the same month as the revenue and posted to the same division (East, West) I am trying to figure out how to use the summary level 'Cost % of Revenue' and apply that to the detailed revenue amount so I get a detailed cost amount.
I was able to calculate a summary table that shows the [StartOfMonth] [Division] [Bike Cost % of Revenue] but I couldn't get it to calculate the final amount and I'm not sure that was even the right way to go about it.
I have uploaded a pbix file with the model so far and since a picture is worth a 1000 words.
Any help would be greatly appreciated.
Thank you
Solved! Go to Solution.
Thank you for the suggestion! Based on that I believe I was able to get to a solution. If I used the measure as is the calc would be off when I included Accessories and I also needed to include more than just the customer in the ALLSELECTED.
First, a measure to calc just Cost % of Revenue for Bikes.
Cost % of Revenue - Bikes =
CALCULATE (
[Cost % of Revenue],
FILTER ( GLAccounts, GLAccounts[LOB] = "Bikes" ),
ALL ( Customers ),
ALL ( Reps )
)
Then a measure to apply the % to the Bike Revenue
Bike Cost 2 = [Bikes Revenue] * CALCULATE ( [Cost % of Revenue - Bikes], ALLSELECTED ( Customers ), ALLSELECTED ( Reps ) )
I have updated the PBIX file if anyone is interesed in the model.
You may try the measure below.
Measure = [Revenue] * CALCULATE ( [Cost % of Revenue], ALLSELECTED ( Sales[Customer] ) )
Thank you for the suggestion! Based on that I believe I was able to get to a solution. If I used the measure as is the calc would be off when I included Accessories and I also needed to include more than just the customer in the ALLSELECTED.
First, a measure to calc just Cost % of Revenue for Bikes.
Cost % of Revenue - Bikes =
CALCULATE (
[Cost % of Revenue],
FILTER ( GLAccounts, GLAccounts[LOB] = "Bikes" ),
ALL ( Customers ),
ALL ( Reps )
)
Then a measure to apply the % to the Bike Revenue
Bike Cost 2 = [Bikes Revenue] * CALCULATE ( [Cost % of Revenue - Bikes], ALLSELECTED ( Customers ), ALLSELECTED ( Reps ) )
I have updated the PBIX file if anyone is interesed in the model.
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 |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |