Sum count of rows ignoring row context

[Carlotta_Fabris]

Hi everyone,

I have two tables, one in which I have different response times, the other contains the different possible bins.

I have a relation between the two, the bin table filters the responses table (1-N).

I want to get the number of responses that are in a specific bin that is multiple of 5. For example for the bin that goes from 00:00:00 to 00:00:05 I have 168 responses.

I tried building a table with the bins in the rows and a simple count as the value, but it doesn't work.
It gets me the count of all the different bins, but I would like to have only the bins that are multiple of 5.

I tried filtering the bins but it doesn't work.

var _BinSize_Start = SELECTEDVALUE(Bins[Value])
var _BinSize_End = (_BinSize_Start + 'Bin Size'[Bin Size Value]) -1
var _ResponsesCount = COUNT(Data[Response Seconds])
var _ResponsesNr = 
CALCULATE(COUNT(Data[Response Seconds]), FILTER(ALL(Data[Response Seconds]), Data[Response Seconds] >= _BinSize_Start && Data[Response Seconds] <= _BinSize_End))
Return _ResponsesNr

Ideally I would like to sum the count of the rows that are between the start and end of the bins.

Any suggestions?

[v-rzhou-msft]

Hi @Carlotta_Fabris ,

I suggest you to create a DimBin table to help your calculation.

DimBin(time) = ADDCOLUMNS(VALUES('Table'[Bins (time)]),"Rank",RANKX(VALUES('Table'[Bins (time)]),[Bins (time)],,ASC))

Bins_Filter_Relation = 
VAR _IF =
    ADDCOLUMNS (
        'DimBin(time)',
        "If",
            VAR _Bin =
                MINX (
                    FILTER (
                        'DimBin(time)',
                        MOD ( 'DimBin(time)'[Rank], 5 ) = 1
                            && 'DimBin(time)'[Rank] >= EARLIER ( 'DimBin(time)'[Rank] )
                    ),
                    [Rank]
                )
            RETURN
                IF ( _Bin = BLANK (), MAX ( 'DimBin(time)'[Rank] ), _Bin )
    )
VAR _RANKX =
    ADDCOLUMNS ( _IF, "Bin", RANKX ( _IF, [If],, ASC, DENSE ) )
RETURN
SUMX (
        FILTER ( _RANKX, [Bins (time)] = EARLIER ( 'DimBin(time)'[Bins (time)] ) ),
        [Bin]
    )

Relationship:

Result is as below.

Best Regards,
Rico Zhou

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

[Carlotta_Fabris]

Thank you for your answer, but I would like to have the total of the different bins that are multiple of 5 in one row of the matrix.

So for example on the row 00:00:00 I would like to see 168, then on the row 00:00:05 220 and so on.

This solution would also be difficult to replicate because then the bin size is dynamic and it changes form 5 to 10 to 15 to 20 etc.

[123abc]

It seems like you want to count the number of responses within bins that are multiples of 5. To achieve this, you can create a measure that calculates the count of responses within the specified bins.

Here's a revised version of your DAX measure:

ResponsesCountMultipleOf5 =
VAR BinStart = SELECTEDVALUE(Bins[Value])
VAR BinEnd = BinStart + SELECTEDVALUE('Bin Size'[Bin Size Value]) - 1
VAR BinMultipleOf5 = TRUNC(BinStart / 5) * 5
VAR ResponsesCount =
CALCULATE(
COUNT(Data[Response Seconds]),
FILTER(
ALL(Data),
Data[Response Seconds] >= BinStart &&
Data[Response Seconds] <= BinEnd
)
)
RETURN
IF(MOD(BinMultipleOf5, 5) = 0, ResponsesCount, BLANK())

Explanation:

1. BinStart and BinEnd are calculated based on the selected bin and its size.
2. BinMultipleOf5 calculates the nearest multiple of 5 for the bin start time.
3. ResponsesCount calculates the count of responses within the specified bin range.
4. The IF statement checks if the bin is a multiple of 5. If it is, it returns the ResponsesCount; otherwise, it returns BLANK().

This measure should give you the count of responses within bins that are multiples of 5. Make sure to use this measure in your visualizations to see the desired counts.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Carlotta_Fabris]

Thank you!

This worked, but now I can't manage to get the correct total.

VAR BinStart = SELECTEDVALUE(Bins[Value])

VAR BinEnd = BinStart + SELECTEDVALUE('Bin Size'[Bin Size]) - 1

VAR BinMultipleOfSize = TRUNC(BinStart / 'Bin Size'[Bin Size Value]) * 'Bin Size'[Bin Size Value]

VAR ResponsesCount =

        CALCULATE(

            COUNT(Data[Response Seconds]),

            FILTER(

                ALL(Data),

                Data[Response Seconds] >= BinStart &&

                Data[Response Seconds] <= BinEnd

            )

        )

RETURN

IF(ISINSCOPE(Bins[Bins (time)]),

    IF(MOD(BinMultipleOfSize, 'Bin Size'[Bin Size Value]) = 0, ResponsesCount, BLANK()),

    CALCULATE(COUNT(Data[Response Seconds]),REMOVEFILTERS(Data[Response Seconds]))

)

I tried like this, but it doesn't work.

Just to give you some more context, I was trying to improve the performances of the matrix but this does't seem to solve the problem. IF you want to read something more on this, read my other post https://community.fabric.microsoft.com/t5/Desktop/Improve-Performances-on-matrix-and-histogram/m-p/3...

Thank you again,
Carlotta.