Forum Discussion

mm5308's avatar
mm5308
Helper I
4 years ago
Solved

Reference a Column Resulting from a Summarize Function, Within the Same Table Function??

Tried searching for this topic for a while but honestly I'm not even sure how to word what I'm trying to accomplish, so I'll explain it.   My main table contains a couple dozen columns and I'm tryi...
Need Help
  • davehus's avatar
    davehus
    4 years ago

    Hi mm5308 ,

     

    Try this column measure to see if it gets you the desired outcome.

     

    Count Batches = CALCULATE(COUNTA('Table'[BatchID]),ALLEXCEPT('Table','Table'[BatchID]))
     

     

     

     

davehus's avatar
davehus
Memorable Member
4 years ago

Hi mm5308 ,

 

I'm not sure if this will help as you are saying that there is a countif so you might have an additional clause to add. You could create a measure like below.

CALCULATE(COUNTROWS(

SUMMARIZE('Tbl',
'Tbl'[BatchID],
'Tbl'[MonthNum])))

 

Let me know if this is on the right track?

 

mm5308's avatar
mm5308
Helper I
4 years ago

Hi davehus ,

I probably should've clarified that I'm looking to create a table - not a measure - that displays all ID and MonthNum values, plus that "count" column I want to add. As far as the "COUNTIF" reference, I only brought it up to illustrate what I would do in Excel. In that environment I'm pretty savvy. Translating that knowledge to the "language" of Power BI, however, is where I struggle. Hope this explanation clarifies, and thanks for your input!

  • davehus's avatar
    davehus
    Memorable Member
    4 years ago

    Hi mm5308 , No problem, have you an excel screengrab of your desired output with dummy data and I'll see what I can do for you. 🙂

    • mm5308's avatar
      mm5308
      Helper I
      4 years ago

      Hi @davehus,

      Thanks for suggestion to add a column and then a formula. I actually never thought of simply adding a column! Seems so obvious now, although I guess I was focused on making that step part of the table creation formula.

      Unfortunately, the formula did not work. Every value was a 1 because I think it was simply counting each row as opposed to adding the qualifier to count "values like the one on the current row". Perhaps the formula needs a FILTER modifier?

      Additionally, please see the screenshot below for an example of what I'm looking for (...in Excel since I can't figure out how to actually do it in PBI yet).

  • davehus's avatar
    davehus
    Memorable Member
    4 years ago

    Hi mm5308 ,

     

    Just had a flashback to something I had to do before. Create your summarize table as outlined. Go to the Data Tab in the designer and then add column with your desired measure referencing the new table. You will need to wrap the measure in calculate, as if you use say a countrows() it will return the total row number in each row