Straight Table min price by Item group

[Anonymous]

Hi Guys

Have a small problem to evaluate the minimum unit price of an item group in my app. The minimu should be the result of the Revenue€ / QTY Sold (#QuantityDelivered)

To evaluate the average im using this expression in the dimension

=num(    Aggr(sum(#Revenue_EUR), [Item group]) /

             Aggr(sum(#QuantityDelivered), [Item group]),0.01)

How can i evaluate the minimum and maximum by Item group?

Many thanks

[Chanty4u]

try this

Min

FirstSortedValue(Aggr(SUM(Itemgroup),filed,value),value)

Max

FirstSortedValue(Aggr(SUM(Itemgroup),filed,value),-value)

[Anonymous]

Did you check this?

Aggr() function

[sunny_talwar · Accepted solution]

May be this:

Min(TOTAL <[Item Group]> Aggr(Sum(#Revenue_EUR)/Sum(#QuantityDelivered), AllDimensionsFromYourChart))

[Anonymous]

This will not work, as im collecting the unit price values from sales orders. Thats mean i have to divide somehow the total sales value by the quantity sold, then i need to display the lowest value.

How ever i tried this solution and i got an "error in calculation"

[Anonymous]

yes, i cant aggr the total sales value and to the same on the qty sold and divid it... This would for sure lead to a missmatch of the result.

[Anonymous]

I think from my basic understandig this could work to get rid of my problem

unfortunatelly i get an error in the calculation did i something wrong?

=Min(TOTAL <[Item group]> Aggr(Sum({$<[Fact process] = {'SI','AL'}>} #Revenue_EUR)/

                                        Sum({$<[Fact process] = {'SI'}>} #QuantityDelivered), %Item))

[sunny_talwar]

Is this a dimension or expression? and what all dimensions you have in your chart?

[Anonymous]

As dim it wil cause en error, as expression it shows the wrong value

Dim are [Item group] and %Item

[sunny_talwar]

May be this as the dimension:

=Aggr(Min(TOTAL <[Item group]> Aggr(Sum({$<[Fact process] = {'SI','AL'}>} #Revenue_EUR)/Sum({$<[Fact process] = {'SI'}>} #QuantityDelivered), %Item, [Item Group])), %Item, [Item Group])

and test this for expression:

=Min(TOTAL <[Item group]> Aggr(Sum({$<[Fact process] = {'SI','AL'}>} #Revenue_EUR)/Sum({$<[Fact process] = {'SI'}>} #QuantityDelivered), %Item, [Item Group]))