Forum Discussion
Top 10% Measure Issues
I have a DIM item table and a Fact purchse table.
I created a measure to calculate the 2022-2023 purchase spend for each item.
I am trying create another measure that calculates 10% of the 2022-2023 purchase spend. I want this measure to stay the same for each item, so that it is a static value. I also want it to be recalculated based on the context of the items. So if it is top 10 items, it would be the sum of 10% of the purchase spend for each row, and if it is top 20 items, same thing.
Here is what I have now:
Total Purchases 2022-2023 =
CALCULATE(
[Total Purchases],
'Date'[Year] IN {2022, 2023}
)
Top10PercentSpend = CALCULATE( [Total Item Purchase Spend 2022-2023] * 0.1,
ALL('Item')
)
Top10PercentSpend item =
SUMX(
SUMMARIZE(
CALCULATETABLE(
'Purchases',
VALUES('Item'[Item #])
),
'Item'[Item #],
"TotalCost", [Total Item Purchase Spend 2022-2023]
),
[Total Item Purchase Spend 2022-2023] * 0.1
)
---
the problem I am running into is getting just the context total. For example, the "Top10PercentSpend" measure is adding up 10% of all purchases no matter the context. However the Top10PercentSpend item measure is calculating the 10% spend for each item individually.
What I really want is the total of the "Top10PercentSpend item" measure in each row.
the problem I am running into is getting just the context total. For example, the "Top10PercentSpend" measure is adding up 10% of all purchases no matter the context. However the Top10PercentSpend item measure is calculating the 10% spend for each item individually.
What I really want is the total of the "Top10PercentSpend item" measure in each row.
Any help is appreciated. I am happy to clarify or provide additional context.
Thanks!
No RepliesBe the first to reply