Count a frequency measure

[kb4514]

I have a measure which counts the number of transactions per id within a timeframe (from slicer):
 
mFrequency = IF(DISTINCTCOUNT(RPT_FACTORDER[TRANSACTION_NUMBER]) = BLANK(), 0,
                        IF(DISTINCTCOUNT(RPT_FACTORDER[TRANSACTION_NUMBER]) = 1, 1,
                        IF(DISTINCTCOUNT(RPT_FACTORDER[TRANSACTION_NUMBER]) = 2, 2,
                        IF(DISTINCTCOUNT(RPT_FACTORDER[TRANSACTION_NUMBER]) = 3, 3, 4))))
 
Which gets me output like this:
 
Id       mFrequency
111    1
222    1
333    3
444    2

555    1
 
Now I need to count up the measure (mFrequency) to get results which look like this, and change based on the slicer timeframe:
 
mFrequency Count
1                    3
2                    1
3                    1
4                    0
 
I'm new to Power BI.  Help is appreciated.  Thanks.

[sturlaws · Accepted solution]

Hi,

since you need frequency on the axis, you need to add a stand-alone table with the neccessary values. And based on those values, count the number of IDs with corresponding frequency.

This measure would look like this

Measure =
VAR _count =
    COUNTROWS (
        FILTER (
            ADDCOLUMNS (
                VALUES ( 'Table'[id] );
                "@freq";
                VAR _trans =
                    CALCULATE ( SUM ( 'Table'[transactions] ) )
                RETURN
                    IF ( _trans > 3; 4; _trans )
            );
            [@freq] = SELECTEDVALUE ( Frequency[F] )
        )
    )
RETURN
    IF ( ISBLANK ( _count ); 0; _count )

Since you have not provided any data or sample report, I have created a mockup dataset and created a demo report based on that dataset: pbix

cheers,

S

[kb4514]

Thanks so much!  It worked perfectly.