How can I count how many times does a count of repeted values repeats in a visual?

[eduvelrod]

Hi all,

I hava a problem which I can´t solve. Does any of you have a solution?

Let me describe the case:

I have a table in which there are 2 columns, one with a "SoftwareID" and the other with "HardwareID". What I need is to identify how many "HardwareIDs" have 2 SoftwareIDs installed, 3 softwareIDs isntalled and so on.

Any sugestion?

Thank you

[rajulshah]

Hello @eduvelrod ,

You can create a measure as follows. For your reference, I have just created the measure for 2 SoftwareIDs.

Hardware with 2 SoftwareIDs = 
VAR SummarizedTable = SUMMARIZE('Table','Table'[HardwareID],"TotalSoftwareIDs",COUNT('Table'[SoftwareID]))
VAR With2SIDs = CONCATENATEX(FILTER(SummarizedTable,[TotalSoftwareIDs]=2),'Table'[HardwareID],",")
RETURN
    With2SIDs

 Please let me know if this isn't working for you.

[eduvelrod]

Hi @rajulshah tahnk you for your answer.

This is near what I want, thanks, but te result I would like to have is another board not only for HardwareIDs with two softwares, I need to have all the cases someting like:

Hardware with 1 sw - 150
Hardware with 2 sw - 100
Hardware with 3 sw - 95
(...)
Hardware with N sw - 2

Is this possible? Thank you very much

[rajulshah · Accepted solution]

@eduvelrod ,

Please create a calculated table as follows:

Software Count = GENERATESERIES(1,100,1)

Rename the column of the calculated table as 'Value of N'.

And then create the measure as follows:

Hardware with N SoftwareIDs =
VAR SummarizedTable =
    SUMMARIZE (
        'Table',
        'Table'[HardwareID],
        "TotalSoftwareIDs", COUNT ( 'Table'[SoftwareID] )
    )
VAR With2SIDs =
    COUNTX (
        FILTER (
            SummarizedTable,
            [TotalSoftwareIDs] = SELECTEDVALUE ( 'Software Count'[Value of N] )
        ),
        'Table'[HardwareID]
    )
RETURN
    With2SIDs + 0

The result will be as follows:

Please let me know if this doesn't work.

[mahenkj2]

Hi @eduvelrod ,

If this is the data:

HardwareIDs	SoftwareIDs
1	1
1	2
1	3
1	4
2	1
3	1
3	2
4	2

Then you can do it in power query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeJYHQjLCM4yhrNMwCwjuDpjFBZEhwmEFQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [HardwareIDs = _t, SoftwareIDs = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"HardwareIDs", Int64.Type}, {"SoftwareIDs", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"HardwareIDs"}, {{"Count", each _, type table [HardwareIDs=nullable number, SoftwareIDs=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.RowCount([Count])),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Custom", "No of Softwares"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"No of Softwares", Int64.Type}, {"HardwareIDs", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Count"}),
    #"Grouped Rows1" = Table.Group(#"Removed Columns", {"No of Softwares"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Grouped Rows1",{{"Count", "No of Hardware Count"}})
in
    #"Renamed Columns1"

Output is as below:

Hope it helps.

[rajulshah]

Hello @eduvelrod ,

So, what I understand here is that you want to count SoftwareIDs by HardwareIDs, right?