Running Count of Answers

[horrellbt01]

I have a list of questions and answers by date. The answers are text, so I need to do a running COUNT rather than total.

Here is an example of my raw data.

Answer Date	Week Number	Answer
1/1/2020	1	Red
1/1/2020	1	Blue
1/8/2020	2	Green
1/8/2020	2	Green
1/15/2020	3	Blue
1/15/2020	3	Blue
1/15/2020	3	Green

What I am looking to return is something like this using the new measure

Answer	Week 1	Week 2	Week 3
Blue	1		3
Red	1
Green		2	3

 

[sandy666]

Answer Date	Week Number	Answer		Answer	1	2	3
01/01/2020	1	Red		Blue	1	0	2
01/01/2020	1	Blue		Green	0	2	1
08/01/2020	2	Green		Red	1	0	0
08/01/2020	2	Green
15/01/2020	3	Blue
15/01/2020	3	Blue
15/01/2020	3	Green

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TDC = Table.DuplicateColumn(Source, "Week Number", "Week Number - Copy"),
    RC = Table.RemoveColumns(TDC,{"Answer Date"}),
    Pivot = Table.Pivot(Table.TransformColumnTypes(RC, {{"Week Number - Copy", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(RC, {{"Week Number - Copy", type text}}, "en-GB")[#"Week Number - Copy"]), "Week Number - Copy", "Week Number", List.Count)
in
    Pivot

or with Pivot Table

 

[sandy666]

or like this

Answer	Week 1	Week 2	Week 3
Blue	1		2
Green		2	1
Red	1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Prefix = Table.TransformColumns(Source, {{"Week Number", each "Week " & Text.From(_, "en-GB"), type text}}),
    Group = Table.Group(Prefix, {"Answer", "Week Number"}, {{"Count", each Table.RowCount(_), type number}}),
    Sort = Table.Sort(Group,{{"Week Number", Order.Ascending}}),
    Pivot = Table.Pivot(Table.TransformColumnTypes(Sort, {{"Week Number", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(Sort, {{"Week Number", type text}}, "en-GB")[#"Week Number"]), "Week Number", "Count", List.Sum)
in
    Pivot

 

[horrellbt01]

or like this

Answer	Week 1	Week 2	Week 3
Blue	1		2
Green		2	1
Red	1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Prefix = Table.TransformColumns(Source, {{"Week Number", each "Week " & Text.From(_, "en-GB"), type text}}),
    Group = Table.Group(Prefix, {"Answer", "Week Number"}, {{"Count", each Table.RowCount(_), type number}}),
    Sort = Table.Sort(Group,{{"Week Number", Order.Ascending}}),
    Pivot = Table.Pivot(Table.TransformColumnTypes(Sort, {{"Week Number", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(Sort, {{"Week Number", type text}}, "en-GB")[#"Week Number"]), "Week Number", "Count", List.Sum)
in
    Pivot

These solutions work, but I am not working in excel for this project. I am looking for a solution in Power BI

 

[sandy666]

I am looking for a solution in Power BI

You didn't say that.
Power BI contain Power Query so you can adapt this code to your needs, it's simple

 

[horrellbt01]

Sorry - That will not work for what I need, the above is just an example. I need this as a measure to use within Power BI and my different datasets. If all I needed was a pivot table, you are correct. With this being a power BI forum I didn't think I needed to include that detail.


After sleeping on it and fresh coffee this morning I was able to figure it out. Here is my solution:

Running Count of Answers =
CALCULATE(
DISTINCTCOUNT('page'[TDLINX ID]),
FILTER(
ALLSELECTED('page'[Week of Year]),
ISONORAFTER('page'[Week of Year], MAX('page'[Week of Year]), DESC)
)
)

 

[sandy666]

It's nice that you worked it out
have a nice day

btw.

Power BI​

Business Intelligence Tools: Power Pivot, Power View, Power Query, DAX Formulas