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
RvdHeijden
Post Prodigy
Post Prodigy

SUMPRODUCT in excel but what is it in BI ?

I need to search a table (per row) for rows that match up to 2 variables 1)Client and 2)a certain tekst value (outcome of a formula)

In excel i would use a simple SUMPRODUCT where i can give the 2 variables and easy peasy.

 

What is the formula in BI ? can someone help me out here ?

16 REPLIES 16
MarcelBeug
Community Champion
Community Champion

Alternatively you can create a dashboard with 2 slicers for your selections and a table for your results.

Specializing in Power Query Formula Language (M)

Hi @RvdHeijden

 

Try SUMX function.

 

SumX ( YourTable , YourTable[Col1] * YourTable[Col2] ) is equivalent to an Excel SumProduct over Col 1 and Col 2.

@Datatouille in Excel you should enter a 'value' but in powerBI you dont ?

For example if you want the know the number of times 'value A' is in Column A and 'Value B' in Column B

 

You need to get a count of such rows?

then look for =CALCULATE(COUNTROWS(Table),Table[Column A]="value A",Table[Column B]="value B")

Or if you need to pass not certain values as filter but measure (or other value), use FILTER as second argument in CALCULATE

Maxim Zelensky
excel-inside.pro

@Datatouille both formulas dont seem to work but maybe i wasn't clear in stating my question

I have a table which has the Client names, the number of tickets and (per ticket) if they were solved within contracttime or not 'Binnen SLA' / 'Buiten SLA' (these values are a result of a formula)

 

I want to filter, per client, (in a different table) the times a tickets was solved within contractime and the number of times we were to late as a percentage of total

For example: Client A has had 50 tickets and 40 were solved in contracttime and 10 were late. That means 80% was on time and 20% was late

 

It can be done in the query editor as illustrated in this video.

 

Code generated during video recording (except for the first 2 lines that were generated already):

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Sumproduct in Excel but what is it in Power BI.xlsx"), null, true),
    Tickets_Table = Source{[Item="Tickets",Kind="Table"]}[Data],
    #"Added Custom" = Table.AddColumn(Tickets_Table, "Binnen SLA", each if [Solved] = "Binnen SLA" then 1 else 0),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Buiten SLA", each if [Solved] = "Buiten SLA" then 1 else 0),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"Client"}, {{"Aantal binnen SLA", each List.Sum([Binnen SLA]), type number}, {"Aantal buiten SLA", each List.Sum([Buiten SLA]), type number}, {"Totaal aantal", each Table.RowCount(_), type number}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Percentage binnen SLA", each [Aantal binnen SLA] / [Totaal aantal], Percentage.Type),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Percentage buiten SLA", each [Aantal buiten SLA] / [Totaal aantal], Percentage.Type)
in
    #"Added Custom3"
Specializing in Power Query Formula Language (M)

@MarcelBeug your solution might work but the problem is that in the 'Edit Queries' you can only edit the colums which you got through 'Get Data' (original data).

The colums which states if a ticket is solved within SLA (or not) is a calculates column and in the Edit Queries you dont see those colums therefore i can't use them.

I've already added 2 colums to the table afterwords with 1) a column to calculate the Datediff between 2 dates and 2) a formula that returns a value 'Binnen SLA' or 'Buiten SLA' depending on the DateDiff

Is this what you want?Снимок.JPG

 

 

 

 

Maxim Zelensky
excel-inside.pro

@hohlick

That is not what i mean but its kind of the same thing.

 

I have a table with al the tickets and a few calculated colums with the datediff between 2 dates and a calculated colum stating if the ticket was closed in time or not.

In a different table i have the client names and in that table i want these extra colums like the way @Marcelbeug mentioned

@RvdHeijden so... you can see this table with clients and desired extra columns in the top table on the picture I've posted.

You need one measure [Total tickets]

Next two formulas is for calculated columns

Maxim Zelensky
excel-inside.pro

@hohlick my apologies, ur last mail worked but i see now that did not get the result i thought i wanted.

i have a stacked column chart where i can see (per month) the number of tickets Binnen/Buiten SLA but they wanted

numbers AND %

 

I need the % in that chart as wel as the amount of tickets and i can't see to fix this with tooltips so i thought i had to use a formula but the Stacked colum chart works with 1 colum with different values like a pie chart so how can i do this ?

@RvdHeijden, give us please more details about your data model.

What is the structure of the table where your Binnen/Buiten SLA columns is?

What is the structure of the table where your monthly data is? It is the same tables?

If you have a single table where the next columns exist:

  1. Client
  2. Date (or Month number)
  3. Ticket No
  4. Ticket Status (Binnen/Buiten SLA)

then there is onee solution. If you have a more complex data model, then solution could be very different.

Maxim Zelensky
excel-inside.pro

@hohlick ill try to be as clear as i can (hopefully Smiley Happy)

 

I have a table 'Tickets' with the al the date colums which i need to calculate, the collums which calculate the datediff (Date A - DateB ) and the column that calculates if a ticket is 'Binnen or Buiten SLA'.

 

I want a Stacked Column Chart that shows per month the percentage (always 100% total) of the tickets that are Binnen SLA and are Buiten SLA.

 

Basically i want a Stacked Column Chart which displays (per month) the percentage of tickets that were Binnen/Buiten SLA

@hohlick

Got any ideas ?

Hi @RvdHeijden

sorry, missed the thread. will try to take annother look at the problem

Maxim Zelensky
excel-inside.pro

@hohlick @Datatouille

 

Any ideas yet ?

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.