Create table report based on SharePoint list

[msago]

Hi, I am a complete newbie at PowerApps, but I have a strong believe it could help me getting table reports to my colleagues on their mobile devices in real time. 
I have a normal SP-list with several columns, but I only want to use a few in order to return a simple report table. 

Here is an example with the structure and some fictional data, but the principle is the same:

Year (choice list)	Contact (choice list: Website; Call; Mail)
2020	Website
2020	Call
2019	Website
2019	Website
2019	Mail
2018	Call

Now I would like it to return a table, combining the 'year' column and counting the 'contact' column. Plus adding a yearly 'total' column.
For example:

Year	Website	Call	Mail	Total
2020	1	1	0	2
2019	2	0	1	3
2018	0	1	0	1

It may seem very simple, but having no experience at all, believe me this is quite a challenge. I have been looking around on the internet, and saw a lot of pieces here and there, but was unable to find the exact example. 

And a small sub question: What if the value isn't in a choice column of my SP-list, but in a lookup table? 

Could anyone point me in the right direction and thereby enforce my initial believe that this could also be used for simple requirements? Highly appreciated ! 🙏
 

[v-xiaochen-msft]

Hi @msago ,

According to your description, I assume:

1. You have a lookup column, called "LookupTest".
2. The column related to the lookup column is the "Test" column, and its data type is Single line of text.
3. You display the result on the data table control.

I've made a test for your reference:

1\ This is my “LIST36” list.

2\ This is my “LIST37” list.

3\ Add a data table control and set its Items property to:

AddColumns(

    AddColumns(

        RenameColumns(

            Choices(LIST36.Year),

            "Value",

            "Year1"

        ),

        "Website",

        CountRows(

            Filter(

                LIST36,

                LIST36[@Year].Value = Year1,

                Contact.Value = "Website"

            )

        ),

        "Call",

        CountRows(

            Filter(

                LIST36,

                LIST36[@Year].Value = Year1,

                Contact.Value = "Call"

            )

        ),

        "Mail",

        CountRows(

            Filter(

                LIST36,

                LIST36[@Year].Value = Year1,

                Contact.Value = "Mail"

            )

        ),

        "111",

        CountRows(

            Filter(

                LIST36,

                LIST36[@Year].Value = Year1,

                LookupTest.Value = "111"

            )

        ),

        "222",

        CountRows(

            Filter(

                LIST36,

                LIST36[@Year].Value = Year1,

                LookupTest.Value = "222"

            )

        )

    ),

    "Total",

    '111' + '222' + Website + Call + Mail

)

4\ The result is as follows:

Best Regards,

Wearsky

[msago]

Hi @v-xiaochen-msft 
Thank you very much for your help !
I've changed the Items property related to my real SPlist (see beneath), but somehow it doesn't really give the expected result. Note: apparently I need to use ;  instead of , as well. Maybe it's regionally bound? 

Could you please have a look at it and advice where it could have gone wrong? 
Thanks a lot ! 😉

[v-xiaochen-msft]

Hi @msago ,

Could you tell me:

Did your formula report an error?

If the formula reports an error, please tell me the content of the error.

In addition, you could try to refresh the data source, or close the app and reopen it.

Best Regards,

Wearsky