cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## SumProduct in measure

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?

19 REPLIES 19
Highlighted
Frequent Visitor

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

Highlighted
Solution Sage

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

Highlighted
Frequent Visitor

i did that and all the numbers are 1's

Highlighted
Solution Sage

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

Highlighted
Frequent Visitor

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.

Highlighted
Frequent Visitor

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

Highlighted
Solution Sage

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?

Ninter

Highlighted
Frequent Visitor

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.

Highlighted
Solution Sage

hi @RCSmart01,

With pleasure we are here to help...

Ninter

Highlighted
Super User III

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

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors