Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RCSmart01
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?

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

View solution in original post

19 REPLIES 19
Interkoubess
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...

 

Ninter

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,

 

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

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

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.

 

Sumtype.PNG

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" 

Capture.JPG

 

Hi @RCSmart01,

 

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

 

Did you try it ?

 

Ninter

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.

 

Ninter

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

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

i did that and all the numbers are 1's

Capture.JPG

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

 

 

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

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

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.

hi @RCSmart01,

 

Please mark your solution and close this subject.

 

With pleasure we are here to help...

 

Ninter

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.