DAX formula : X bottom sales = X Top Sales

[mrhuguru]

Hey everyone,

I am looking for a DAX Formula helping me calculate how many bottom sales are needed to equal my previously calculated Top N Sales.

Is this possible ?

Thank you so much,

Hugo

 

[Matt Allington]

So if I understand the problem. You have a top n measures - let's say top 5. That equals some value - let's say 100. Do you want to know how many products make 100 starting from he worst and work up (I've bottom n) or do you want to know how many from 6+?

either way it sounds like a goal seek (I don't think you can do that). Or you could hand create a stack of measures (top 15, top 16, top 17 etc ) and see which one is double the top 5.

 

[mrhuguru]

Hey Matt,

Thank you for your answer, I meant the first statement : how many of the worst transactions to equal the value of the top 5, 10, X sales. I would definitely like it to be dynamic so the value of the top sales and the number of worst sales to equal that value would change thanks to filters.

 

[Matt Allington]

Well I wouldn't like to say never, but I don't know of any way to do this in DAX.

 

[mrhuguru]

Well, thank you anyway Matt !

Have a nice one,
Hugo

 

[ImkeF]

To me this looks as if it can be done in Power Query. But then you would need to hit the Data-Refresh-All button after you've changed the slicer selection (and wait for the query to be refreshed) (or write a VBA macro that triggers the refresh of the query automaticall). Would this be an option for you?

 

[mrhuguru]

Definitely an option !

 

[ImkeF]

This is an example of how you can use the slicers that control your Power-Pivot results to control your Power Query results as well (slicer: Date). And to pass an individual parameter to your query as well (F1). Just type in your TopX-number.

It returns the factor of times the TopN sales are bigger than the corresponding BottomN sales.

It would also be possible to return a table with several years, if that’s what you need. The selection process would need to be shifted to a function.

The slicer selection are harvested in the hidden pivot-table in column I and passed to PQ (query: SlicerSelection1). There merged with an InnerJoin to act as a filter. Watch out for the named ranges (slicer selection and TopX), they are essential in order to pass these items to PQ.

let
    Source = Tabelle1,
    #"Merged Queries" = Table.NestedJoin(Source,{"Date"},SlicerSelection1,{"SlicerDate"},"NewColumn",JoinKind.Inner),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"NewColumn"}),
    Custom1 = List.Sum(List.FirstN(#"Removed Columns"[Value], TopN))/List.Sum(List.LastN(#"Removed Columns"[Value], TopN))
in
    Custom1

LinkToFile

 

[Ozeroth]

Here is one DAX solution:

There are some options in how you define the measures, so just treat this as a template

Assume the model contains these two tables:
Transactions
[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD]Transaction ID[/TD]
[TD="align: right"]Amount[/TD]
[/TR]
[TR]
[TD]T001[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]T002[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]T003[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD="align: right"]...[/TD]
[/TR]
</tbody>[/TABLE]
NumTopTransactions
[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD]NumTopTransactions[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]...[/TD]
[/TR]
</tbody>[/TABLE]

For this particular method to work, a dimension over which to count the TopN etc is needed. I've used Transaction ID.

Define these measures:

[B]Transaction Amount[/B] :=
SUM( Transactions[Amount] )

[B]TopN Selection[/B] :=
IF (
    HASONEVALUE ( NumTopTransactions[NumTopTransactions] ),
    VALUES(NumTopTransactions[NumTopTransactions] )
)

[B]TopN Transaction Amount[/B] :=
CALCULATE(
    [Transaction Amount],
    TOPN( [TopN Selection], ALLSELECTED(Transactions[Transaction ID]), [Transaction Amount] )
)

[B]NumBottomTransactions required to at least equal TopN[/B] := 
IF (
    NOT( ISBLANK( [TopN Selection] ) ),
    DISTINCTCOUNT( Transactions[Transaction ID] ) +1 - 
        COUNTROWS (
            FILTER(
                ADDCOLUMNS (
                    VALUES ( Transactions[Transaction ID] ),
                    "CumAmount",
                    CALCULATE (
                        [Transaction Amount],
                        FILTER (
                            ALLSELECTED ( Transactions[Transaction ID] ),
                            [Transaction Amount] <=
                                CALCULATE ( [Transaction Amount], Transactions[Transaction ID] = EARLIER( Transactions[Transaction ID], 2))
                        )
                    )
                ),
            [CumAmount] >= [TopN Transaction Amount]
        )
    )
)

I've assumed that the TopN is calculated over the currently selected transactions (ALLSELECTED used in a couple of places).

The last measure

1. Constructs a table containing cumulative transaction amount for each Transaction ID
2. Counts the number of transactions with cumulative Transaction Amount (summing smallest to largest) >= TopN Transaction Amount
3. Subtracts this count from the total number of transactions and adds 1. This gives the number of transactions required to just equal or exceed the TopN Transaction Amount.
   You could handle this differently depending how you want boundary cases handled, for example instead count transactions with CumAmount <= TopN Transaction Amount...it will only matter in cases where the bottom transactions don't exactly equal to TopN Transaction Amount.

NumTopTransactions can be selected by slicer or otherwise filtered.

 

[Matt Allington]

I should have know that Ozeroth would nail this .

What at I have learnt is that people with a strong SQL background have the thinking style, experience and skill to nut out these complex "table" problems. We're lucky to have such help .