Getting count at summary level

[MayurM]

Hi,

I have a table with raw data of contracts with details like contract ID, date, client name, bill, cost, etc. Sample data is given as  - 

I created a measure to calculate margin. What I wanted was like this - 

I was able to categorize the clients in margin brackets using a measure. But now I cannot use that measure in line or bar chart to give me the count of clients under each bracket for the year. I tried creating a second table but it will not be connected to existing slicers.

[v-xinruzhu-msft · Accepted solution]

Hi @MayurM 

You can refer to the following solution.

The sample data is the same as you provided.

1.Create a new table.

Margin brackets =
VAR _add1 =
    ADDCOLUMNS ( GENERATESERIES ( 0, 0.9, 0.1 ), "EndNum", [Value] + 0.1 )
RETURN
    ADDCOLUMNS (
        _add1,
        "Range",
            FORMAT ( [Value], "percent" ) & "-"
                & FORMAT ( [EndNum], "percent" )
    )

2.I change the margin measure to the following.

Margin =
VAR a =
    SUMX (
        FILTER ( ALLSELECTED ( 'Table' ), [Client] IN VALUES ( 'Table'[Client] ) ),
        [Bill]
    )
VAR b =
    SUMX (
        FILTER ( ALLSELECTED ( 'Table' ), [Client] IN VALUES ( 'Table'[Client] ) ),
        [Cost]
    )
RETURN
    DIVIDE ( a - b, a )

3.Create a new measure to calculate the count.

Margin_Count =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Client] ),
    FILTER (
        'Table',
        [Margin] >= MAX ( 'Margin brackets'[Value] )
            && [Margin] < MAX ( 'Margin brackets'[EndNum] )
    )
)

4.Create the related visual ,e.g bar chart.

Put the following field to the viual.

Output

It can filtered by the date or other slicer.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MayurM]

Thank you! It works...