Calculating Mode for a calculated value ( temporary Table)

Question

I am struggeling since a while with the Problem to calculate th mode for [OrderQuantity] which is not a field of my Factstable FactIncSince FactsInc is the Report of the Incomes in ourIncomedepartment it is possible that One order is splited to different Lots, so&nbsp;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 tablesVAR TempTab1 = ADDCOLUMNS(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VALUES(FactsInc[OrderNr.]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; " OrderQuantity ",CALCULATE(SUMX(FactsInc, FactsInc[Quantity]))&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )//represents a Table with the two columns [OrderNr.] and [Quantity]; this Quantity is now representing what i called [OrderQuantity] beforeAt the next step I wanted to create a second Table showing how often each OrderQuantity occours [Frequency]&nbsp; in TempTab1VAR TempTab2 = ADDCOLUMNS(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; VALUES(TempTab1[OrderQuantity]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; " 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 · Answer

Hi Mario_the_Diver ,
&nbsp;
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.
&nbsp;

mario_the_diver · Answer

Sorry to answer late ( had some days off)Your&nbsp;code is hard to understand for mevar tempTab2=&nbsp;&nbsp;&nbsp; ADDCOLUMNS (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TempTab1,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "Frequency", CALCULATE (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DISTINCTCOUNT ( FactsInc[OrderNr.] ), &nbsp;//&nbsp;Why Counting the OrderNr. in FactsInc which is not filtered by&nbsp;comming FILTER&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FILTER ( TempTab1, [ OrderQuantity ] = EARLIER ( [ OrderQuantity ] ) ) //EARLIER because of Calculate&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )&nbsp;&nbsp;&nbsp;&nbsp; )

Forum Discussion

Calculating Mode for a calculated value ( temporary Table)

2 Replies

Related content

MoM calculation

Calculation Help

Calculate Tooltips

Hour Calculation

Calculating with Filters