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

Counting Repeated Details

Hi  all,

 

Sample file: Sample PBIX

 

I have a table like this,

 

new3.PNG

 

I would like to show a Card visual like this,

new2.PNG

 

If I select an ID in slicer as "1", I need to have values shown on the card. Please ref the below screenshot,


NEW.PNG

 

How can I acheive the above result?

 

Thanks in Advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Counting Repeated Details

@praveenkumarv 

Edited

Create the measure below and place it in a  card visual

Check it out in this file

 

NewMeasure =
VAR _Total =
    COUNT ( Sales[Detail] )
VAR _AuxTable =
    ADDCOLUMNS (
        SUMMARIZE ( Sales; Sales[ID]; Sales[Detail] );
        "_Count"; CALCULATE ( COUNT ( Sales[Detail] ) )
    )
VAR _MaxCount =
    MAXX ( _AuxTable; [_Count] )
VAR _TotalMax =
    SUMX ( FILTER ( _AuxTable; [_Count] = _MaxCount ); [_Count] )
VAR _OutputText =
    CONCATENATEX (
        FILTER ( _AuxTable; [_Count] = _MaxCount );
        [_Count] & " - " & [Detail];
        UNICHAR ( 10 )
    )
VAR _FirstLineText = _TotalMax & "/" & _Total
VAR _AllText =
    _FirstLineText & UNICHAR ( 10 ) & _OutputText
RETURN
    _AllText

 

View solution in original post

6 REPLIES 6
praveenkumarv Regular Visitor
Regular Visitor

Re: Counting Repeated Details

Can the RANKX function will work for this kind of problem?

Super User
Super User

Re: Counting Repeated Details

@praveenkumarv 

 

How is Maximum Detail Value computed?

praveenkumarv Regular Visitor
Regular Visitor

Re: Counting Repeated Details

Hi Zubair,
The maximum detail is the max count of texts in detail column. Here, in this example, the slicer is showing ID=2, for this selection, we need to find out the maximum of Detail column.
So, here, the repeated count is Low Price which is two times. Hence, MAX value is = 2.


New4.png

 

 

Highlighted
Super User
Super User

Re: Counting Repeated Details

@praveenkumarv 

Edited

Create the measure below and place it in a  card visual

Check it out in this file

 

NewMeasure =
VAR _Total =
    COUNT ( Sales[Detail] )
VAR _AuxTable =
    ADDCOLUMNS (
        SUMMARIZE ( Sales; Sales[ID]; Sales[Detail] );
        "_Count"; CALCULATE ( COUNT ( Sales[Detail] ) )
    )
VAR _MaxCount =
    MAXX ( _AuxTable; [_Count] )
VAR _TotalMax =
    SUMX ( FILTER ( _AuxTable; [_Count] = _MaxCount ); [_Count] )
VAR _OutputText =
    CONCATENATEX (
        FILTER ( _AuxTable; [_Count] = _MaxCount );
        [_Count] & " - " & [Detail];
        UNICHAR ( 10 )
    )
VAR _FirstLineText = _TotalMax & "/" & _Total
VAR _AllText =
    _FirstLineText & UNICHAR ( 10 ) & _OutputText
RETURN
    _AllText

 

View solution in original post

praveenkumarv Regular Visitor
Regular Visitor

Re: Counting Repeated Details

Thank you for your reply. This solution is not dynamic hence gives worng values.
How can we count the repeated values on a single column?

v-yulgu-msft Super Contributor
Super Contributor

Re: Counting Repeated Details

Hi @praveenkumarv ,

 

Have tested with AlB 's suggestion, it returns dynamical result as expected. If it doesn't meet your requirement, what is your desire output?

1.PNG2.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 375 members 3,505 guests
Please welcome our newest community members: