Frequency Count of Names in Power BI "Table" visualization

[ksrini]

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.

Name	Date	Quantity	State	City
Apple	1/12/2022	20	New York	New York City
Orange	12/23/2021	30	California	Los Angeles
Orange	12/3/2021	20	California	Los Angeles
Banana	11/13/2021	10	New Jersey	Trinton
Apple	10/24/2021	40	New York	New York City
Orange	10/4/2021	20	California	Los Angeles
Banana	9/14/2021	10	New Jersey	Trinton
Apple	9/16/2021	20	New York	New York City
Orange	9/16/2021	30	California	Los Angeles
Orange	9/16/2021	20	California	Los Angeles
Grapes	9/18/2021	10	South Carolina	Columbia
Grapes	9/20/2021	40	South Carolina	Columbia
Orange	10/4/2021	20	California	Los Angeles
Banana	9/14/2021	10	New Jersey	Trinton

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.

Name	Date	Quantity	State	City	Frequency	Expected Count
Apple	1/12/2022	20	New York	New York City	2	3
Orange	12/23/2021	30	California	Los Angeles	2	6
Orange	12/3/2021	20	California	Los Angeles	2	6
Banana	11/13/2021	10	New Jersey	Trinton	1	3
Apple	10/24/2021	40	New York	New York City	1	3
Orange	10/4/2021	20	California	Los Angeles	1	6
Banana	9/14/2021	10	New Jersey	Trinton	1	3
Apple	9/16/2021	20	New York	New York City	1	3
Orange	9/16/2021	30	California	Los Angeles	1	6
Orange	9/16/2021	20	California	Los Angeles	1	6
Grapes	9/18/2021	10	South Carolina	Columbia	1	2
Grapes	9/20/2021	40	South Carolina	Columbia	1	2
Orange	10/4/2021	20	California	Los Angeles	1	6
Banana	9/14/2021	10	New Jersey	Trinton	1	3

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

[v-xiaotang]

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]

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

[ksrini]

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 ?

[v-xiaotang]

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.

[ksrini]

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

[v-xiaotang · 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]))

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.