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

 

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

 

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?

Community Support Team
Community Support Team

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.