cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Basia Regular Visitor
Regular Visitor

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

Accepted Solutions
Basia Regular Visitor
Regular Visitor

Re: COUNTIFS on top of another calculation

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
Super User
Super User

Re: COUNTIFS on top of another calculation

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]

Basia Regular Visitor
Regular Visitor

Re: COUNTIFS on top of another calculation

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.

Super User
Super User

Re: COUNTIFS on top of another calculation

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] )
)

Basia Regular Visitor
Regular Visitor

Re: COUNTIFS on top of another calculation

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

Super User
Super User

Re: COUNTIFS on top of another calculation


@Basia 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] )
)
Basia Regular Visitor
Regular Visitor

Re: COUNTIFS on top of another calculation

@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

 

Super User
Super User

Re: COUNTIFS on top of another calculation

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.

Community Support Team
Community Support Team

Re: COUNTIFS on top of another calculation

Hi @Basia 

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


@Basia 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.

Basia Regular Visitor
Regular Visitor

Re: COUNTIFS on top of another calculation

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

 

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 158 members 1,794 guests
Please welcome our newest community members: