Forum Discussion

adaocabelo's avatar
adaocabelo
Frequent Visitor
7 years ago
Solved

Restricting Values for Total

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 -...
adaocabelo's avatar
adaocabelo
Frequent Visitor
7 years ago

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's avatar
Greg_Deckler
Community Champion
7 years ago

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's avatar
    adaocabelo
    Frequent Visitor
    7 years ago

    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).

      • adaocabelo's avatar
        adaocabelo
        Frequent Visitor
        7 years ago

        Thanks Frank,

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