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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
ksrini
Helper I
Helper I

Frequency Count of Names in Power BI "Table" visualization

Hi All,

 

I have a Table visualization with Names and quantity sold on daily basis and number of times it is sold on the day. I want to show count of Names appearing in that table which is again based on filters. I am able to get 1 count for each entry but not the total number of times it appeared. Request your help.

 

NameDateQuantityStateCity
Apple1/12/202220New YorkNew York City
Orange12/23/202130CaliforniaLos Angeles
Orange12/3/202120CaliforniaLos Angeles
Banana11/13/202110New JerseyTrinton
Apple10/24/202140New YorkNew York City
Orange10/4/202120CaliforniaLos Angeles
Banana9/14/202110New JerseyTrinton
Apple9/16/202120New YorkNew York City
Orange9/16/202130CaliforniaLos Angeles
Orange9/16/202120CaliforniaLos Angeles
Grapes9/18/202110South CarolinaColumbia
Grapes9/20/202140South CarolinaColumbia
Orange10/4/202120CaliforniaLos Angeles
Banana9/14/202110New JerseyTrinton

 

I am looking to get the above data into Summary with Frequency of Transactions per Fruit in a "Table" view and not a Matrix. Please see below.

 

NameDateQuantityStateCityFrequencyExpected Count
Apple1/12/202220New YorkNew York City23
Orange12/23/202130CaliforniaLos Angeles26
Orange12/3/202120CaliforniaLos Angeles26
Banana11/13/202110New JerseyTrinton13
Apple10/24/202140New YorkNew York City13
Orange10/4/202120CaliforniaLos Angeles16
Banana9/14/202110New JerseyTrinton13
Apple9/16/202120New YorkNew York City13
Orange9/16/202130CaliforniaLos Angeles16
Orange9/16/202120CaliforniaLos Angeles16
Grapes9/18/202110South CarolinaColumbia12
Grapes9/20/202140South CarolinaColumbia12
Orange10/4/202120CaliforniaLos Angeles16
Banana9/14/202110New JerseyTrinton13

 

I have created a Frequency Measure = SUMX(VALUES(Sales[Name]), CALCULATE(COUNT(Sales[Duplicate Name]))).

I tried few other and also tried the formula shared below answer. But i am not able to get the correct answer. I am mostly getting "1" as frequency but not the "Expected Count" which i derived in Excel using COUNTIF. Also there are filters applied on the table like Category which includes Fruits, Vegetables, Grocery as different catagories. Can anyone help me on it. 

 

Thanks,

SK

1 ACCEPTED SOLUTION

Hi @ksrini 

Thanks for your reply.

Try this, create this measure and put it into visual

Measure = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Name]))

vxiaotang_0-1643089680587.png

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-xiaotang
Community Support
Community Support

Hi @ksrini 

Have you solved this question with bcdobbs's help? If you have solved the question, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

If you need more help, please provide:

(1) a sample file, you can replace raw data with bogus data to protect your privacy.

(2) or provide some sample data that fully covers your issue/question

(3) give your expected result based on the sample you provide

Kindly note: Please ensure the data in sample is concise and representative.

Thanks.

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

bcdobbs
Super User
Super User

I think this is what you're asking for: 
(Replace Table1 with your table name)

Count of Names = 
    CALCULATE (
        COUNTROWS ( Table1 ),
        ALLSELECTED( Table1 ),
        VALUES ( Table1[Name] )
    )


Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Thanks for the solution, unfortunately it didnt work. I can't upload files but i have given the data and expected output above. Can you check the data once ?

Hi @ksrini 

Thanks for your reply. I've read your new description, but still struggling to understand how to calculate Frequency & Expected Count, please provide more details,

my question is:

(1) Do you mean you want to create measures to calculate Frequency & Expected Count? 

(2) for the first row in table visual, Name = Apple, Date = 1/12/2022, why Frequency = 2? why Expected Count = 3?

     and for the second row, Name = Orange, why Frequency =2? why Expected Count = 6? 

Thanks.

You can @ my account in reply, so that I won't miss your message.

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

@v-xiaotang Thanks for checking. Purpose is to count the number of transactions we have on each fruit in the Table visualization. Apple appears 3 times and Orange 6 times in the table on different dates. So i am calculating the total number of times a product is appears in the table. Please ignore Frequency. Just see Expected Count.  In excel i can achieve this Expected count with Countif ($A$2:$A$14,A2) and dragging it for all rows. Hope i made it clear. Thanks again.

Hi @ksrini 

Thanks for your reply.

Try this, create this measure and put it into visual

Measure = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Name]))

vxiaotang_0-1643089680587.png

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.