Dax formula for counting and grouping weekly transactions by frequency

[cinlod]

Hi,

Please, i need help. Need a DAX formula 

I have over 100 different services in a daily transaction record. I want to count and group each service by frequency by 1 Transaction, 2 Transactions, 3 Transactions, 4 Transactions, above 4 Transactions on a weekly basis. In order to plot visuals.

Many Thanks

[fhill]

Could you please post some sample raw data, and a mock up of your desired output?  Thank You

[cinlod]

Hi Fhill,

Thanks for your response.

Please, kindly find attached sample data. I want to count the frequency of Vehicle Numble and group the frequency into 1 Transaction, 1 Transaction, 2 Transactions, 3 Transactions, and Above 4 Transactions.

Many Thanks

Transport/ D-Note No	Volume (t)	Km	Vehicle Number	Date	Driver	Region	Week	Freight Cost
01115290801	30	101	AA464MDG	42739	ABUBAKAR INUSA	North East	2	76041.79
01115465701	30	627	AA343NFD	42757	UMAR ISAH	North West	4	267867
01115317101	30	402	AA340NFD	42742	ABDULLAHI MOHD	North West	2	207682
01115359601	30	402	AA340NFD	42746	adamu ali	North West	3	207682
01115392401	30	402	AA340NFD	42748	SULE AHMAD	North West	3	207682
00824318101	40	147	AAA107XN	42756	TN-009302325/JUDE IYORIOBHE	West	4	184683.2
00930107601	40	330	AAA107XN	42748	JUDE IYORI	South West	3	304498.4
00930115701	30	155	AAA205XA	42748	SULAIMON QUADRI	West	3	105346.6
00930119601	30	107	AAA135XP	42748	TN-008242203-KAZEEM LATEEF	Lagos Mainland	3	107417.5
00930188701	40	70	AAA107XN	42754	JUDE IYORIOBHE	Lagos Island	4	156938.4
01014220201	30	84.2	AAA155XN	42739	ABIODUN LAWAL	West	2	102009.6
01014226801	30	80.4	AAA205XA	42740	SULAIMAN QUADRI	West	2	102009.6
01014246201	30	80.4	AAA205XA	42743	SULAIMAN	West	2	102009.6
01014247101	30	18.3	AAA155XN	42743	abiodun lawal	West	2	66162.51
01014284901	29.31	68.8	AAA10XK	42748	RASHEED SHARAFA	Lagos Island	3	84916.65
01014293901	32.78	68.8	AAA10XK	42750	RASHEED SHARAFA	Lagos Island	3	84916.65
01014303101	30	59	AAA155XN	42752	ABIODUN LAWAL	West	4	62400.25
01014323401	30	43	AAA205XA	42753	SULE QUADRI	Lagos Mainland	4	66162.51

[Zubair_Muhammad]

@cinlod

Do you need output like this?

[cinlod]

Hi Fhill,

Your output is wonderful. I which to get a weekly result of this output such that if i group the frequency into, Example:

Week "X"                      Frequency (Total count)

1 Transaction                  23

2 Transactions                 12

3 Transactions                  7

4 Transactions                  4

Above 4 Transactions       1

Many Thanks,

[Eric_Zhang · Accepted solution]

---

@cinlod wrote:

Hi Fhill,

Your output is wonderful. I which to get a weekly result of this output such that if i group the frequency into, Example:

Week "X"                      Frequency (Total count)

1 Transaction                  23

2 Transactions                 12

3 Transactions                  7

4 Transactions                  4

Above 4 Transactions       1

Many Thanks,

---

@cinlod

To  get the expected output, you may have to create an auxiliary table as below

Then create a measure as 

Frequency (Total count) =
VAR summizedTable =
    SUMMARIZE (
        yourTable,
        yourTable[Vehicle Number],
        "transaction cnt", COUNT ( yourTable[Vehicle Number] )
    )
VAR addcolumn =
    ADDCOLUMNS (
        summizedTable,
        "frequence", SWITCH (
            TRUE (),
            [transaction cnt] = 1, "1 Transaction",
            [transaction cnt] = 2, "2 Transactions",
            [transaction cnt] = 3, "3 Transactions",
            [transaction cnt] = 4, "4 Transactions",
            "Above 4 Transactions"
        )
    )
RETURN
    COUNTROWS (
        FILTER ( addcolumn, [frequence] = MAX ( FrequenceTbl[Frequence] ) )
    )

So finally

See more in the attached pbix file.

[Shelley]

@Eric_Zhang This was very helpful. I think I'm on the right track now. I just wish I understood what each DAX expression was doing, so I could absorb this better. Thank you very much!