Forum Discussion
Getting count at summary level
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.
Hi Anonymous
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.
2 Replies
Hi Anonymous
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.