Calculating Mode for a calculated value ( temporary Table)

[Mario_the_Diver]

I am struggeling since a while with the Problem to calculate th mode for [OrderQuantity] which is not a field of my Factstable FactInc

Since FactsInc is the Report of the Incomes in ourIncomedepartment it is possible that One order is splited to different Lots, so there might be more then one line for one OrderNr. in my facstable. [OrderQuantity] can be Calculated as the SUM of FactsInc[Quantity]

calculated for each Distinct Ordernuber.

I thougth to do this with variables which are representing tables

VAR TempTab1 = ADDCOLUMNS(
                VALUES(FactsInc[OrderNr.]),
                        " OrderQuantity ",CALCULATE(SUMX(FactsInc, FactsInc[Quantity]))
                    )
//represents a Table with the two columns [OrderNr.] and [Quantity]; this Quantity is now representing what i called [OrderQuantity] before

At the next step I wanted to create a second Table showing how often each OrderQuantity occours [Frequency]  in TempTab1

VAR TempTab2 = ADDCOLUMNS(
                VALUES(TempTab1[OrderQuantity]),
                        " Frequency",CALCULATE(COUNTX(TempTab1,TempTab1[OrderQuantity])))

But that does not work.

I’ve got the message „Die TempTab1-Tabelle wurde nicht gefunden“ translated „did not found Table TempTab1“

Do i have to insert a real calculated table to my Model ???

[v-frfei-msft]

Hi @Mario_the_Diver ,

We can update the formula as below.

Table = 
VAR TempTab1 =
    ADDCOLUMNS (
        VALUES ( FactsInc[OrderNr.] ),
        " OrderQuantity ", CALCULATE ( SUMX ( FactsInc, FactsInc[Quantity] ) )
    )
var tempTab2=
    ADDCOLUMNS (
        TempTab1,
        "Frequency", CALCULATE (
            DISTINCTCOUNT ( FactsInc[OrderNr.] ),
            FILTER ( TempTab1, [ OrderQuantity ] = EARLIER ( [ OrderQuantity ] ) )
        )
    )
return
tempTab2

For more details, please check the pbix as attached.

[Mario_the_Diver]

Sorry to answer late ( had some days off)

Your code is hard to understand for me

var tempTab2=
    ADDCOLUMNS (
        TempTab1,
        "Frequency", CALCULATE (
            DISTINCTCOUNT ( FactsInc[OrderNr.] ),  // Why Counting the OrderNr. in FactsInc which is not filtered by comming FILTER
            FILTER ( TempTab1, [ OrderQuantity ] = EARLIER ( [ OrderQuantity ] ) ) //EARLIER because of Calculate
        )

    )