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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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