Count Rows per Group in Table

[charlyrosero]

Hi everyone

i've this dataset, i need to count the rows per group (Area) and after multiply by column value (Weight). i try this isn't works

Rows per Area = CALCULATE(COUNT(BSC[AREA]),ALLEXCEPT(BSC,BSC[KPI]))

Rows per IT Area = 3, Rows per Financial Area =2.

Month	Area	KPI	Weight	Rows per Area	Result	FORMULA
January	IT	KPI 1	0,3	3	0,9	(Weight (0,3) * Quantity Kpi Per Area (3)
January	IT	KPI 2	0,25	3	0,75	(Weight (0,75) * Quantity Kpi Per Area (3)
January	IT	KPI 3	0,48	3	1,44	(Weight (0,48) * Quantity Kpi Per Area (3)
January	Financial	KPI 4	0,1	2	0,2	(Weight (0,1) * Quantity Kpi Per Area (2)
January	Financial	KPI 5	0,85	2	1,7	(Weight (0,85) * Quantity Kpi Per Area (2)

[danextian]

Hi @charlyrosero ,

Your current formula is the rows per KPI. Change it to 

Rows per Area =
CALCULATE ( COUNT ( BSC[AREA] ), ALLEXCEPT ( BSC, BSC[Area] ) )

If you need rows per are per month per kpi, use below formula

Rows per Area =
CALCULATE (
    COUNT ( BSC[AREA] ),
    ALLEXCEPT ( BSC, BSC[Month], BSC[Area], BSC[KPI] )
)

[v-diye-msft · Accepted solution]

Hi @charlyrosero 

Try below measures:

Measure = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Area]))

Measure 2 = [Measure]*MAX('Table'[Weight])

 

Pbix attached.

[amitchandak]

I am not sure about the role of formula. But seems like you need subtotal

Measure = CALCULATE(COUNT('Table'[Area]),ALLEXCEPT('Table','Table'[Area],'Table'[Month]))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin