Get Count of Measure for Aggregate Bins in Power BI

0
votes

I have below data :

enter image description here

I have created 2 measures in dax for Product Count and Amount Total.

Product Count=DistinctCount(table[Product])

Amount Total=sum(table[Amount])

enter image description here

I want to show output as below:

enter image description here

For that I have created 2 measures Product density band and Amount band using switch case. But I am not able get the count of ids for each band. Please help.

1
powerbidax

1 Answers

1
votes

We need two configuration tables

The Amount Band table

enter image description here

And the Product Density Band table

Product Density Band table

now we can define two measures to count the amounts and product counts in the configuration tables selection

Amount Total Band = 
COUNTROWS(
    FILTER(
        ALL( 'Table'[Id] ),
        [Amount Total] > MIN( 'Amount Band'[From] )
            && [Amount Total] <= MAX( 'Amount Band'[To] )
    )
)

and

Product Count Density Band = 
COUNTROWS(
    FILTER(
        ALL( 'Table'[Id] ),
        [Product Count] >= MIN( 'Product Density Band'[Product Density Band] )
            && [Product Count] <= MAX( 'Product Density Band'[Product Density Band] )
    )
)

these measures used in a Table visual with the configuration tables give the desired result

resulting table visuals