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

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

Highlighted

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

Highlighted

i did that and all the numbers are 1's

Capture.JPG

Highlighted

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

 

 

Highlighted

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

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

Highlighted

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

Highlighted

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

Highlighted

hi @RCSmart01,

 

Please mark your solution and close this subject.

 

With pleasure we are here to help...

 

Ninter

Highlighted

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/

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors