Restricting Values for Total

[adaocabelo]

Hi guys, I'm having some difficulties to have the totals on my report.

I have a table which has several repeated rows, as the example bellow:

Product - Value

P1 - $500,00

P1 - $500,00

P1 - $500,00

P1 - $500,00

P1 - $500,00

P2 - $300,00

P2 - $300,00

P2 - $300,00

P2 - $300,00

P3 - $800,00

P3 - $800,00

Total = $5300,00

So far I have the right total, since it's using all rows to sum, but I need to sum the values only once per product, then, the right total is $1600,00.

I've made this measure to calculate:

Measure = IF(opaRametro[opaRametro Value]=0; DIVIDE(value;COUNT(Product)))

and the result was this:

Product - Value

P1 - $500,00

P2 - $300,00

P3 - $800,00

Total - $481,81

Instead of adding the values of this, the system is dividing the total of the original column and dividing per the original number of rows: $5300,00/11 = $481,81.

How should I resolve this?

[Greg_Deckler]

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

[adaocabelo]

Hi Greg, thanks for your reply.

This was very helpfull to change the total value, but I'm still getting same result as before.

Power BI is still adding all row on the total, instead of only the rows that appears.

[Greg_Deckler]

Correct, because the total row executes in the context of ALL. For this to work, you need to calculate the total row in the context of ALLSELECTED.

[adaocabelo]

Sorry, but, how do I use that?
Think my filter is a bit harder to apply then the one on the example.

On the example the filter was "amount >1000", I need to filter only one result for each product and sum that.
All Selected function is not working for this purpose.

How should I refer on the filter to have these results?

[Greg_Deckler]

The example is just that, an example. Each formula is going to be different. For example, your total line might be something like:

= ... DIVIDE(SUMX(ALLSELECTED(Table),[Value]),COUNTX(ALLSELECTED(Table),[Value]))

Or, now that I am looking at this a little closer, couldn't you just use a standard Average aggregation? Or is Value a measure?

[adaocabelo]

Hi Greg,

thanks for your patience.

The total I need is not an average, because I need to sum only one value per product and the average produces a diferent result.

I've tried to make an paramater to index the lines, my idea was to sum only the rows with index = 0, but I've notice that Power BI has created the same number of lines for each index. For an example, if product 1 has 32 lines, power BI creates 32 index 0, 32 index 1...

Just to try to be more clear, from my first example: $5300,00(sum of all rows), $481,81(average) and R$1600,00(sum of one value, or price, per product).

[v-frfei-msft · Accepted solution]

Hi @adaocabelo,

Here I made an sample as your description. I created two measures to meet your requirement.

CA1L = MAX(opaRametro[Value])/CALCULATE(COUNT(opaRametro[Product]))

Measure = SUMX(opaRametro,[CA1L])

Then we can get the result as we excepted.

For more details, please check the pbix as attached.

https://www.dropbox.com/s/eakbjcoo32a90l6/Restricting%20Values%20for%20Total.pbix?dl=0

Regards,

Frank

[adaocabelo]

Thanks Frank,

this solution really works for my example, I could replicate that here.