Apply calculation for distinct value then aggregate it

[Anonymous]

Hello everyone,

Basically I have restaurants which are opened a certain number of days per months (different numbers according to each restaurant).

Here is the formula which works perfectly on lower levels (where restaurants are distinct:

Auvs = CALCULATE(Divide(SUM('Single Items'[Pmix]),DISTINCTCOUNT('Calendar'[Date].[Day])),FILTER('Single Items','Single Items'[Sales]<>0))

The problem is if I aggregate, let's say two restaurants together, with one opened 10 days and the other 15 days it will divide everything by 15;

So I am looking for a solution where I do the calculation for each restaurant then aggregate (I cannot do a unique measure for each restaurant, there are just too many).

If anyone has any idea that would help me a lot 🙂

[v-huizhn-msft]

Hi @Anonymous,

Could you please share your sample table and list the expected result for further analysis?

Best Regards,
Angelia

[Anonymous]

Hello Angelie,

First thank you for your answer!

So here I enclose a quick example that illustrates what I would like to happend (in green) and what actually happen (in yellow).

You have a restaurant A and a restaurant B

A is opened 30 days and has 3000$ of sales

B is opened 20 days and has 1000$ of sales

What I compute is sales/days, so for A 3000/30 = 100 and for B 1000/20 = 50. So if I would like to aggregate both restaurants together I would like to obtain (3000/30) + (1000/20) = 150.

But if aggregate restaurants together it will only take the highest number of days so in my example 30 days. In the calculation it gives: (3000 + 1000)/30 = 133.

So I would like to find a way for power bi to follow the first path and dividing sales by day first then aggregate instead of aggregating and dividng by the days.

I hope it is clear :).

[v-huizhn-msft]

Hi @Anonymous,

Please try the following formula.

result =
VAR restaurant =
    FIRSTNONBLANK ( Table5[restaurant], Table5[restaurant] )
RETURN
    SUMX (
        Table5,
        CALCULATE (
            SUM ( Table5[Sales] ) / SUM ( Table5[days] ),
            FILTER ( Table5, Table5[restaurant] = restaurant )
        )
    )

Best Regards,
Angelia

[Anonymous]

Hello Angelia,

Sorry for my late answer, I didn't have time those last days to check the formula.

So I looked into and tried to adapt it to my real data which gives this:

result =
VAR restaurant =
FIRSTNONBLANK(Stores[Restaurant],Stores[Restaurant])
Return
SUMX (
'Single Items',
CALCULATE (
CALCULATE(SUM('Single Items'[Pmix])/DISTINCTCOUNT('Single Items'[Date]),FILTER('Single Items',SUM('Single Items'[Sales])<>0)),FILTER(Stores,Stores[Restaurant]=restaurant))

)

Unfortunately, it does not loop through the restaurants does it for the first one (so the ifrst non blank) but hten stop there.

[Anonymous · Accepted solution]

For those interested finally found the solution combining sumx and distinct:

Auvs = SUMX (DISTINCT('Single Items'[Restaurant]),CALCULATE(SUM('Single Items'[Pmix])/DISTINCTCOUNT('Single Items'[Date])))