Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

COUNTIFS on top of another calculation

Hi,

I've looked through all the countif solutions here, but my issue seems a bit different.

 

Basically the spreadsheet report has a pivot table on top of a data set. The Pivot is Name and Max(Frequency)

The Countif references that table and puts the frequencies into buckets: 1,2,3,4,5+.

In SQL I would have solved this issue with a dense rank with over (partition by).

 

The issue I have is that my count is counting underlying rows in the data table, instead of counting at face value.

How do I ignore underlying transactions?

 

I'm using the following calc for #/bucket:

#/bucket = COUNTAX('DAILY SALES',
CALCULATE (
MAXA('DAILY SALES'[Frequency Factor Bucket]),
FILTER (
'DAILY SALES',
'DAILY SALES'[Frequency Factor Bucket] = EARLIER('DAILY SALES'[Frequency Factor Bucket] )
)
)
)

Below I have the frequency I want to count, my current result in yellow, and the result I want in orange.

The current calc in yellow is counting all the underlying transactions too.

Capture.PNG

Thanks in advance!

Basia

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi All, 

I have solved the issue myself.

It was mainly to do with context and my misunderstanding of it.

 

Solution is:

COUNTAX (
ALLSELECTED (Adviser[Adviser Name] ),
CALCULATE ( COUNTA (Sales[Frequency Factor Bucket]) )
)

 

Basia

View solution in original post

12 REPLIES 12
d_gosbell
Super User
Super User

Are you just trying to count the number of rows per bucket? If so something like the following might work:

 

#/bucket = CALCULATE(
  COUNTROWS('DAILY SALES'),

  ALLEXCEPT('DAILY SALES','DAILY SALES'[Frequency Factor Bucket] )
)

 

It does a count of rows ignoring all other filters appart from the [Frequency Factor Bucket]

Anonymous
Not applicable

Thanks @d_gosbell 

This looks promising, except I want to count per bucket, so "grouping" by bucket.

I've tried:

Countif = CALCULATE(
COUNTROWS('DAILY SALES'),
ALLEXCEPT('DAILY SALES','DAILY SALES'[Frequency Factor Bucket]),
FILTER (
'DAILY SALES',
'DAILY SALES'[Frequency Factor Bucket] = EARLIER('DAILY SALES'[Frequency Factor Bucket] )
) )

But this doesn't work 😞 because it comes up with calculation error: earlier which refers to a row context that doesn't exist. I've tried Max instead of earlier, but have had no luck.

Aargh, ALLEXCEPT will work if you are using a slicer, but it's not picking up the context for the current bucket from the row. If you use this alternative measure syntax it should work in both scenarios

 

#/bucket = CALCULATE(
  COUNTROWS('DAILY SALES'),

  ALL('DAILY SALES'),VALUES('DAILY SALES'[Frequency Factor Bucket] )
)

Anonymous
Not applicable

Thanks for giving it a shot @d_gosbell appreciate it.

I'm not getting what I need, which is, to just count at high level how many rows are presented and not count all the records that make up those records.

This is what I'm currently getting (below), and what I need is 17 for bucket 3 and 18 for bucket 4.

Back to the drawing board for me!

Capture.PNG

Cheers,

Basia


@Anonymous wrote:

Thanks for giving it a shot @d_gosbell appreciate it.

I'm not getting what I need, which is, to just count at high level how many rows are presented and not count all the records that make up those records.

 


Oh so that looks like you have filters applied to other columns/tables and you only want to count the selected rows in each bucket. I did not realise that's what you meant by "counting at face value" in your initial post as it was not evident in your first screenshot.

 

You should be able to do this easily by replacing ALL with ALLSELECTED.

 

eg.

 

#/bucket = CALCULATE(
COUNTROWS('DAILY SALES'),
ALLSELECTED('DAILY SALES'), VALUES('DAILY SALES'[Frequency Factor Bucket] )
)
Anonymous
Not applicable

@d_gosbell  Yes, sorry for not being clear!

AllSELECTED seems to be heading in the right direction but not quite there.

Capture.PNG

Perhaps I'll have to think of something else, maybe a custom table - not ideal, but I have to come up with what the business needs.

Thanks again for your help.

 

Basia

 

Hi @Anonymous 

As tested, this formula works

Column = CALCULATE(COUNTROWS(Table1),ALLEXCEPT(Table1,Table1[frequency]))

3.png

 

The following don't work well

Column 2 = CALCULATE(COUNTROWS(Table1),ALLSELECTED(Table1),VALUES(Table1[frequency]))

Column 3 = CALCULATE(COUNTROWS(Table1),ALL(Table1),VALUES(Table1[frequency]))

 

As for your formula


@Anonymous wrote:

This looks promising, except I want to count per bucket, so "grouping" by bucket.

I've tried:

Countif = CALCULATE(
COUNTROWS('DAILY SALES'),
ALLEXCEPT('DAILY SALES','DAILY SALES'[Frequency Factor Bucket]),
FILTER (
'DAILY SALES',
'DAILY SALES'[Frequency Factor Bucket] = EARLIER('DAILY SALES'[Frequency Factor Bucket] )
) )


you could modify it as below so to work well

Column 4 = CALCULATE(COUNTROWS(Table1),FILTER(ALLEXCEPT(Table1,Table1[frequency]),Table1[frequency]=EARLIER(Table1[frequency])))

or
Column 5 = CALCULATE(COUNTROWS(Table1),FILTER(ALL(Table1),Table1[frequency]=EARLIER(Table1[frequency])))

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Hi @d_gosbell @,
Thanks again to you both for your time.

Sorry, took me a bit of time to compile this. At the end of this message is a subset of the underlying data. 


Basically, my SSAS tabular model has a COLUMN calc that works out the New Business Frequency.

Once the frequency has been calculated, it then needs to be put into buckets.

New Business Frequency: =
IF (
'DAILY SALES'[New Business Investor?] = "Y",
CALCULATE (
DISTINCTCOUNT ( 'DAILY SALES'[Investor Number + Product] ),
FILTER (
'DAILY SALES',
'DAILY SALES'[Contact ID] = EARLIER ( 'DAILY SALES'[Contact ID] )
),
FILTER (
'DAILY SALES',
'DAILY SALES'[Financial Year] = EARLIER ( 'DAILY SALES'[Financial Year] )
),
'DAILY SALES'[New Business Investor?] = "Y"
),
BLANK ()
)

I've made the default "Summarize By" as Max in the SSAS model.

I put this into buckets in another COLUMN calc:

Frequency Factor Bucket:=
IF (
'DAILY SALES'[New Business Frequency] = 1,
"1",
IF (
'DAILY SALES'[New Business Frequency] = 2,
"2",
IF (
'DAILY SALES'[New Business Frequency] = 3,
"3",
IF (
'DAILY SALES'[New Business Frequency] = 4,
"4",
IF ( 'DAILY SALES'[New Business Frequency] >= 5, "5+", BLANK () )
)
)
)
)


The business currently use Excel pivot and do a Max of the frequency.
They then have a separate CountIf to put those frequencies into a bucket.

Capture.PNG

 

My Frequency calc works well at a granular level in Power BI. But, as soon as I try to group it by any other column, the max numbers don't make sense because I actually want a count of how many 1's and 2's etc that I have, not the max value.

Capture.PNG

So I need something that will count the number of frequencies in each bucket as shown by the above Excel screenshot of pivot + CountIF calc to put in buckets.

Problem is that all of the calcs you have both provided, take into account all of the transaction records and count them as well! So I end up getting large numbers (see my very first post)

Capture.PNG

Here's a sample of the data set. In reality, it is much much bigger than this:

Transaction IdFinancial YearNameContact IDMonthQuarterInvestor Number + ProductAdviser policy frequency
611954332019 John  gRnAAK Jul-182019 Q1  
611951432019 Anton  JqxAAE Jul-182019 Q1  
611951152019 Hubert  ZR7AAN Jul-182019 Q138674-TA4
611951392019 Hubert  WHsAAN Jul-182019 Q1  
611951672019 Hubert  tOBAAY Jul-182019 Q1  
611947772019 Hubert  ZR7AAN Jul-182019 Q1  
611947692019 Blake  DkzAAG Jul-182019 Q1  
611948552019 Blake  DkzAAG Jul-182019 Q1  
611951732019 Anton  9M2AAI Jul-182019 Q1  
611957282019 Anton  DE4AAM Jul-182019 Q161718-TA3
611950692019 Anton  1hPAAS Jul-182019 Q198256-TA5
611957242019 Nat  XpkAAG Jul-182019 Q1  
611948172019 Josh  1wpAAC Jul-182019 Q134333-TA5
611948892019 Blake  3ZxAAK Jul-182019 Q134358-TA1
611949292019 Anton  wbtAAA Jul-182019 Q134366-TA2
611950672019 Josh  93mAAA Jul-182019 Q134390-TA2
611950712019 Hubert  ryWAAS Jul-182019 Q134408-TA1
611950792019 Anton  1hPAAS Jul-182019 Q134432-TA5
611953232019 Anton  VPKAA2 Jul-182019 Q134598-TA3
611953962019 Anton  VPKAA2 Jul-182019 Q134598-TA3
611954752019 Nat  cqSAAR Jul-182019 Q134655-TA3
611954992019 Hubert  K3EAAW Jul-182019 Q134663-TA2
611955382019 Anton  ioPQAS Jul-182019 Q134770-TA4
611956392019 Hubert  j24QAC Jul-182019 Q134812-TA3
611957262019 Nat  WNfAAM Jul-182019 Q134838-TA9
611951952019 Anton  HGbAAM Jul-182019 Q1  
611951632019 Hubert  LdlAAH Jul-182019 Q1  
611957342019 Blake  3RNQAZ Jul-182019 Q1  
611948872019 Hubert  SNBAA2 Jul-182019 Q134341-LA1
611949492019 Anton  wbtAAA Jul-182019 Q134366-LA2
611950812019 Dimi  zQlAAK Jul-182019 Q134424-LA3
611950992019 Anton  xs6AAC Jul-182019 Q134440-LA3
611951372019 Anton  UgHAAW Jul-182019 Q134531-LA7
611951412019 Anton  UgHAAW Jul-182019 Q1  
611951472019 Anton  UgHAAW Jul-182019 Q1  
611951592019 Anton  UgHAAW Jul-182019 Q134531-LA7
611954232019 Anton  VPKAA2 Jul-182019 Q134598-LA3
611954432019 Nat  K7PAAW Jul-182019 Q134614-LA3
611954452019 Josh  cFBAAY Jul-182019 Q134630-LA13
611954932019 Hubert  K3EAAW Jul-182019 Q134663-LA2
611952472019 Blake  2AhAAK Jul-182019 Q134580 -C3
611955032019 Blake  BodAAE Jul-182019 Q134713-LA2

 

When you talk about "calcs" are you referring to calculated columns or measures? From what I can see you should be able to create your buckets using a calculated column, then create a simple measure that has =COUNTROWS('DAILY SALES') as the expression and you should get the results that you are after. I can't actually test properly using the sample data you provided as it's missing the [New Business Investor] column, but if create a calculated column to generate buckets over the [Adviser policy frequency] column I get the results I expect

 

2019-07 rowcounts.png

Anonymous
Not applicable

Yeah, I'm referring to column calculations. I did try a "over partition by" for the adviser frequency, but got some weird large numbers.

Maybe I can go back to the original calc and just divide by the number of transactions I have so that I get the correct value at the rolled up level.

Anyway, I'm doing Dax training currently, so hopefully I'll find something that delivers the result! Smiley Happy

Anonymous
Not applicable

Hi All, 

I have solved the issue myself.

It was mainly to do with context and my misunderstanding of it.

 

Solution is:

COUNTAX (
ALLSELECTED (Adviser[Adviser Name] ),
CALCULATE ( COUNTA (Sales[Frequency Factor Bucket]) )
)

 

Basia

Sorry, I thought I had figured out what you were trying to do, but obviously not. It does not sound like what you are trying to do should be too hard, I must just be missing something.

 

If you'd like more help on this I would suggest the following:

 

  1. Paste in 10-20 rows of example data (as text not a screenshot), it does not need to be real data - you can make up the values, but it would need to be a similar structure to your real data. It will be easier for you to work with if you keep the table & column names the same as your real model
  2. Explain or provide screenshots of any filters that are applied
  3. Show the results from our most recent measure
  4. Show the expected results for the dataset from step 1

Then myself or someone else on the forum can paste the data into a model on our machine and figure out what the issue is.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.