Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Thanks in advance!
Basia
Solved! Go to Solution.
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
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]
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] )
)
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!
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.
@d_gosbell Yes, sorry for not being clear!
AllSELECTED seems to be heading in the right direction but not quite there.
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]))
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.
Hi @d_gosbell @v-juanli-msft,
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.
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.
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)
Here's a sample of the data set. In reality, it is much much bigger than this:
Transaction Id | Financial Year | Name | Contact ID | Month | Quarter | Investor Number + Product | Adviser policy frequency |
61195433 | 2019 | John | gRnAAK | Jul-18 | 2019 Q1 | ||
61195143 | 2019 | Anton | JqxAAE | Jul-18 | 2019 Q1 | ||
61195115 | 2019 | Hubert | ZR7AAN | Jul-18 | 2019 Q1 | 38674-TA | 4 |
61195139 | 2019 | Hubert | WHsAAN | Jul-18 | 2019 Q1 | ||
61195167 | 2019 | Hubert | tOBAAY | Jul-18 | 2019 Q1 | ||
61194777 | 2019 | Hubert | ZR7AAN | Jul-18 | 2019 Q1 | ||
61194769 | 2019 | Blake | DkzAAG | Jul-18 | 2019 Q1 | ||
61194855 | 2019 | Blake | DkzAAG | Jul-18 | 2019 Q1 | ||
61195173 | 2019 | Anton | 9M2AAI | Jul-18 | 2019 Q1 | ||
61195728 | 2019 | Anton | DE4AAM | Jul-18 | 2019 Q1 | 61718-TA | 3 |
61195069 | 2019 | Anton | 1hPAAS | Jul-18 | 2019 Q1 | 98256-TA | 5 |
61195724 | 2019 | Nat | XpkAAG | Jul-18 | 2019 Q1 | ||
61194817 | 2019 | Josh | 1wpAAC | Jul-18 | 2019 Q1 | 34333-TA | 5 |
61194889 | 2019 | Blake | 3ZxAAK | Jul-18 | 2019 Q1 | 34358-TA | 1 |
61194929 | 2019 | Anton | wbtAAA | Jul-18 | 2019 Q1 | 34366-TA | 2 |
61195067 | 2019 | Josh | 93mAAA | Jul-18 | 2019 Q1 | 34390-TA | 2 |
61195071 | 2019 | Hubert | ryWAAS | Jul-18 | 2019 Q1 | 34408-TA | 1 |
61195079 | 2019 | Anton | 1hPAAS | Jul-18 | 2019 Q1 | 34432-TA | 5 |
61195323 | 2019 | Anton | VPKAA2 | Jul-18 | 2019 Q1 | 34598-TA | 3 |
61195396 | 2019 | Anton | VPKAA2 | Jul-18 | 2019 Q1 | 34598-TA | 3 |
61195475 | 2019 | Nat | cqSAAR | Jul-18 | 2019 Q1 | 34655-TA | 3 |
61195499 | 2019 | Hubert | K3EAAW | Jul-18 | 2019 Q1 | 34663-TA | 2 |
61195538 | 2019 | Anton | ioPQAS | Jul-18 | 2019 Q1 | 34770-TA | 4 |
61195639 | 2019 | Hubert | j24QAC | Jul-18 | 2019 Q1 | 34812-TA | 3 |
61195726 | 2019 | Nat | WNfAAM | Jul-18 | 2019 Q1 | 34838-TA | 9 |
61195195 | 2019 | Anton | HGbAAM | Jul-18 | 2019 Q1 | ||
61195163 | 2019 | Hubert | LdlAAH | Jul-18 | 2019 Q1 | ||
61195734 | 2019 | Blake | 3RNQAZ | Jul-18 | 2019 Q1 | ||
61194887 | 2019 | Hubert | SNBAA2 | Jul-18 | 2019 Q1 | 34341-LA | 1 |
61194949 | 2019 | Anton | wbtAAA | Jul-18 | 2019 Q1 | 34366-LA | 2 |
61195081 | 2019 | Dimi | zQlAAK | Jul-18 | 2019 Q1 | 34424-LA | 3 |
61195099 | 2019 | Anton | xs6AAC | Jul-18 | 2019 Q1 | 34440-LA | 3 |
61195137 | 2019 | Anton | UgHAAW | Jul-18 | 2019 Q1 | 34531-LA | 7 |
61195141 | 2019 | Anton | UgHAAW | Jul-18 | 2019 Q1 | ||
61195147 | 2019 | Anton | UgHAAW | Jul-18 | 2019 Q1 | ||
61195159 | 2019 | Anton | UgHAAW | Jul-18 | 2019 Q1 | 34531-LA | 7 |
61195423 | 2019 | Anton | VPKAA2 | Jul-18 | 2019 Q1 | 34598-LA | 3 |
61195443 | 2019 | Nat | K7PAAW | Jul-18 | 2019 Q1 | 34614-LA | 3 |
61195445 | 2019 | Josh | cFBAAY | Jul-18 | 2019 Q1 | 34630-LA | 13 |
61195493 | 2019 | Hubert | K3EAAW | Jul-18 | 2019 Q1 | 34663-LA | 2 |
61195247 | 2019 | Blake | 2AhAAK | Jul-18 | 2019 Q1 | 34580 -C | 3 |
61195503 | 2019 | Blake | BodAAE | Jul-18 | 2019 Q1 | 34713-LA | 2 |
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
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!
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:
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |