Count Categories of Similar Sums

[jabrillo]

Table 1 - Sample data

Category	Sub-Category	Allocation
A	10	0.5
A	20	0.5
B	10	0.75
C	10	0.5
C	20	0.25
D	10	0.25
D	20	0.25
D	30	0.25
E	30	1.0

Table 2 - Sum by Category (using Table visual)

Category	Sum of Allocation
A	1.0
B	0.75
C	0.75
D	0.75
E	1.0

Table 3 - Count of Category by Sum of Allocation

Sum of Allocation	Count
0.75	3
1.0	2

How can I produce the third table? I tried using calculate(sum(allocation, filter(table, category = earlier(category)))), but the result sums all allocations.

[ERD]

@jabrillo , from your example and provided demo table:

1. Create a calculated column in your data table:

Allocation per category = 
CALCULATE ( SUM ( T[Allocation] ), ALLEXCEPT ( T, T[Category] ) )

2. Create a measure:

Allocation Count = 
VAR __t = SUMMARIZE ( T, T[Category], T[Allocation per category] )
VAR __t2 = SUMMARIZE ( T, T[Category], T[Sub-Category], T[Allocation] )
RETURN
    IF (
        ISFILTERED ( T[Sub-Category] ),
        COUNTAX ( __t2, [Category] ),
        COUNTAX ( __t, [Category] )
    )

 

[tackytechtom]

Hi @jabrillo ,

Here a solution by creating a calculated table using DAX:

Here the code for the calculated table:

New Table = 
VAR _helpTable =
SUMMARIZE (
    'Table',
    'Table'[Category],
    "Sum of Allocation", SUM ( 'Table'[Allocation] )
)
RETURN
SUMMARIZE (
    _helpTable,
    [Sum of Allocation],
    "Count",
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Category] ),
        FILTER (
            VALUES ( 'Table'[Category] ),
            CALCULATE ( SUM ( 'Table'[Allocation] ) ) = [Sum of Allocation]
        )
    )
)

I did something similar explained here:

https://www.tackytech.blog/how-to-crack-the-mystery-of-the-mighty-dax/#count-occurrence-of-attribute...

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[jabrillo]

I tried it and I was hoping the slicers would be in effect, but they weren't. The sample data has other columns with associated slicers. I tried to use a filtered table on the first SUMMARIZE but it didn't work. On the second SUMMARIZE, shouldn't I just count the 'Table'[Category] from the first SUMMARIZE? I tried this:

SUMMARIZE (

    _helpTable,

    [Sum of Allocation],

    "Count",

    distinctcount('Resource Allocation'[Res Name])

but this didn't work too.