Showing results for 
Search instead for 
Did you mean: 
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?


Accepted Solutions

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.

View solution in original post

Solution Sage
Solution Sage

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...



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.


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

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?




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

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.



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" 



Hi @RCSmart01,


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


Did you try it ?



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"?

Hi @RCSmart01,


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



Helpful resources

November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!


Power Platform October Community Highlights

Check out the top community contributors across all of the communities


Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors