CODEWITHSUNDEEP
powerbidaxsummarize
Sweepster
Sweepster

Reputation: 1949

Power BI DAX How to add column to a calculated table that summarizes another

I Have a TestTable that summarizes a table Receipts on the Month column and adds a column that counts the number of times (occurence) that each month appears in the Receipts Table.

TestTable = SUMMARIZE(Receipts, Receipts[Month], "TotalReceiptsIssuedInThisMonth", SUM(Receipts[Receipts Issued]), "OccurenceOfMonth", COUNT(Receipts[Month]))

enter image description here

I want to add two columns to this TestTable which will tell me the following:

I know I can click "New Column" and use these formulas:

AvgPercentageReceiptsIssuedInThisMonth = TestTable[TotalReceiptsIssuedInThisMonth]/TestTable[TotalReceiptsIssued]

TotalReceiptsIssued = SUM(TestTable[TotalReceiptsIssuedInThisMonth])

enter image description here

However, I need to integrate those two columns directly into the original TestTable formula to make it all happen in one step for use as a variable in the original Receipts table (otherwise I end up with circular logic if I try using relationships).

I've tried the following:

TestTable = SUMMARIZE(PPTs, PPTs[Month], "TotalReceiptsIssuedInThisMonth", SUM(PPTs[PPTs Issued]), "OccurenceOfMonth", COUNT(PPTs[Month]), "TotalReceiptsIssued", SUM(TestTable[TotalReceiptsIssuedInThisMonth]), "AvgPercentageReceiptsIssuedInThisMonth", TestTable[TotalReceiptsIssuedInThisMonth]/TestTable[TotalReceiptsIssued])

but this returns an error saying "A single value for column 'TotalReceiptsIssuedInThisMonth' in table 'TestTable" cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result." and I've tried:

TestTable = 
VAR first = SUMMARIZE(Receipts, Receipts[Month], "TotalReceiptsIssuedInThisMonth", SUM(Receipts[Receipts Issued]), "OccurenceOfMonth", COUNT(Receipts[Month]))
VAR second = SUM(TestTable[TotalReceiptsIssuedInThisMonth])
VAR third = first[TotalReceiptsIssuedInThisMonth]/second
RETURN
third

But this returns an error saying "The variable'first' cannot be used in current context because a base table is expected."

So my question is, how do I go about combining these three steps into one DAX formula?

Upvotes: 3

Views: 4293

Answers (2)

edward rotich
edward rotich

Reputation: 1

Check out More regarding the use of DAX to create a table https://www.advancelearnlinux.com/how_to_create-table-in-microsoft-power-bi-using-dax-function/

Upvotes: 0

Mik
Mik

Reputation: 2103

I would do something like this. I prefer ADDCOLLUMN(SUMMARIZE()...), because it helps to avoid a miscontexting. As you need a var table, then you need the CALCULATE in ADDCOLUMNS, as it adds the row context.

VAR TestTable =
    ADDCOLUMNS(
            SUMMARIZE(
                    Receipts
                    ,Receipts[Month]
        )
        ,"TotalReceiptsIssuedInThisMonth",CALCULATE(SUM(Receipts[Receipts Issued])) 
        ,"OccurenceOfMonth", CALCULATE(COUNT(Receipts[Month]))
        ,"TotalReceiptsIssued ",SUM(Receipts[Receipts Issued])
         )

RETURN
    ADDCOLUMNS(
        TestTable 
        ,"AvgPercentageReceiptsIssuedInThisMonth"           
               ,DIVIDE([TotalReceiptsIssuedInThisMonth],[TotalReceiptsIssued])
    )

Upvotes: 2

Related Questions