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

user_910737 0
Viewed 430

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:

  • Sum the TotalReceiptsIssuedInThisMonth of the TestTable and show the value in each row
  • For each Month (row), divide the, TotalReceiptsIssuedInThisMonth by the SumOfTotalReceiptsIssued

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?

summarize dax powerbi
2 Answers

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])
    )
user_190423790
user_117584670
Related