SumProduct in measure

[RCSmart01]

i am fairly new to this and i created a "dashboard" in excel but know i want to learn how to put it in power bi, i ahve everything else i just need help on this last one.i am creating an "index" in excel i put this formula in =IF(M25=TRUE,"",SUMPRODUCT(($D$2: D25=D25)*($M$2:M25<>TRUE))) i would like to transferr that over to power bi. how would i do that?

[Interkoubess]

Hi @RCSmart01,

With CALCULATE  and FILTER you can easily process a product with fields( columns).

Please share dummy data and I can make a try...

Ninter

[RCSmart01]

so what i would ike to do is number each item that is in each type but have it start with its own type (see below), i have the filters that i want and removed the items i don't need. So right now i have the items i need. so i want to number the ones that are on screen.

1	Title 3	Book
2	Title 7	Book
3	Title 11	Book
4	Title 15	Book
5	Title 19	Book
6	Title 23	Book
7	Title 27	Book
8	Title 31	Book
1	Title 1	Movie
2	Title 5	Movie
3	Title 9	Movie
4	Title 13	Movie
5	Title 17	Movie
6	Title 21	Movie
7	Title 25	Movie
8	Title 29	Movie
1	Title 4	Music
2	Title 8	Music
3	Title 12	Music
4	Title 16	Music
5	Title 20	Music
6	Title 24	Music
7	Title 28	Music
8	Title 32	Music
1	Title 2	TV Show
2	Title 6	TV Show
3	Title 10	TV Show
4	Title 14	TV Show
5	Title 18	TV Show
6	Title 22	TV Show
7	Title 26	TV Show
8	Title 30	TV Show

[Ashish_Mathur]

Hi @RCSmart01,

In the Query Editor, enter this M code

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Titles", type text}, {"Type", type text}}),
    Partition = Table.Group(#"Changed Type", {"Type"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Titles", "Index"}, {"Titles", "Index"})
in
    #"Expanded Partition"

Here is the result

[Interkoubess]

Hi @RCSmart01,

Your table has no titles ( fields) so I don't know what is item. I can figure out but...

Do you want to count the number of item for every type?

Ninter

[RCSmart01]

oh sorry bout that the column titles are next to buy, titles, and type. and yes count by type

[Interkoubess]

Hi @RCSmart01,

Then you can use COUNTROWS or COUNTA or a combination with calculate and then you select a table visualisation with type and you have your expected figures.

[RCSmart01]

Sorry i should have been more clear i want to label it title with a number and the number restarts by type. See below, i have created something in tableu, and i want to do the same or similar thing in powerbi. then i am going to filter but the number of the "index" 

[Interkoubess]

Hi @RCSmart01,

The formula still works ( you can combine with calculate with sum).

Did you try it ?

Ninter

[RCSmart01]

i don't know what the format of the forumla is, like i said i haven't used powerbi and still trying to get used to it. What do i put in "Sample"?

[Interkoubess]

Hi @RCSmart01,

Sample here is the name of my table so you have to replace it by the name of your actual table.

Ninter

[RCSmart01]

ok come to realize i already did that lol, now with the calulate how would i do the filters?

[Interkoubess]

Choose a visualization ( Table or matrix) and drop your fields ( type, ect...) then you will the results...

[RCSmart01]

i did that and all the numbers are 1's

[Interkoubess]

What are you expecting because we count the number of movie?

[RCSmart01]

can i use the ranking function to get what i want?

[Interkoubess]

I used ranking based on measure like revenues ( sum of revenue), etc...

I used to add index in Power Query by doint a partition of the table.

Are you familiar with Power Query?

Otherwise other members could help you with Dax formula.

Ninter

[RCSmart01 · Accepted solution]

no but i got exactly what i want going through the query editor, didn't even have to use index or custom columns! Thanlks for all your help and i apologize for wasting your time on trying to help me.

[Interkoubess]

hi @RCSmart01,

Please mark your solution and close this subject.

With pleasure we are here to help...

Ninter

[RCSmart01]

i want to show a list of numbers like 1,2,3,4,5,6,7,8 besides the titles, but each time a number, kinda like an index. if you look at my first pic i showed of what i want to do each title has a number by it. not just all ones.