Forum Discussion
Iteration Troubles
Hello!
I've been having trouble with the iteration and subtotal context of my visual.
Objective: When a singular SKU filter is selected from the user, show the values within the context of the selected SKU.
Here, for instance, as we can see in the Total Quantidades Vendidas there are different subtotals above the cost center code, when they should all display 6.248,00 since I have selected only one SKU (166072).
I have 5 dimensions as indexes.
In the Quantidades Vendidas (Sold Quantities) we have the correct output when filtering;
Total Quantidades Vendidas (Total Sold Quantities) should bring the totals of quantities sold within the Cost Center Code... As we can see, this is happening but not on the totals.
Quantidades Vendidas
Quantidades Vendidas =
VAR Data = SELECTEDVALUE(dCalendario[Mês/Ano])
VAR Result =
CALCULATE(
SUM(factKE30[Quantidade Total]), factKE30[Rateio] = "Rateio", dCalendario[Mês/Ano] = Data,
ALLCROSSFILTERED(factKE30),
TREATAS(VALUES(dimProdutos[SKU]), factKE30[Artigo])
)
RETURN Result
Total Quantidades Vendidas
Total Quantidades Vendidas =
VAR Total =
CALCULATE(
[Quantidades Vendidas]
,ALL(dimProdutos[SKU])
)
RETURN Total
I've tried to iterate the Total Quantidades Vendidas:
Total quantidades vendidas II =
IF (
ISINSCOPE(dimProdutos[SKU]),
[Total Quantidades Vendidas],
SUMX(SUMMARIZE(dimProdutos, [SKU],"__Value", [Total Quantidades Vendidas]), [__Value])
)
But with no success and I've tried other strategies also.
Thank you in advance.
3 Replies
nogstavo I'm not entirely certain what you want here in terms of your results. Here is something that might help:
Matrix Measure Total Triple Threat Rock & Roll - Microsoft Power BI Community
First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8Thank you, I'm currently reading your link and trying to understand how it could help me.
Greg_DecklerI have Sales from SKU 166072 within Cost Center Code, and Total Sales from all SKU's within Cost Center Code (6.248,00).
I expect that when a single sku is selected at a filter level, all the subtotals show the correspondent value from that singular selection.
nogstavo Well, the post is meant to give you complete control over subtotal calculations at any level in a hierarchy within a matrix. Do you have sample data that you could share to recreate this issue as it is very difficult to understand what could be going on here.
