Reduce by percent if column value equals something.

[fodelement]

Hello all,

I am new to Power BI, and thanks to the helpful posts on here, I managed to catch up pretty quickly, so thanks for that!

One issue I cannot find an answer to is as follows. I have an internal SharePoint site, that has filable form. 

Once the user enters in the money collected. sharepoint adds up the total and puts in into a calculated colmun called "Total".

The field that says "Took on behalf of" is a personal look up.

What I am looking for, is a measure in Power BI, that if the coumn "Took on Behalf of" = someone's name (because it can be blank) take 80% off of this entries total, and add it to another coulmn.

If I explain why we do this, maybe it will help you understand what I am trying to accomplish. 

Each time a user takes a payment, they record it in sharepoint using this form. If the user is taken a payment for a client that belongs to another user, they get 20% of it, and the user who the client belongs to gets 80% of it. 

Using this measure, I managed to get 80% of the total cash. So I assume some kind of if statement will be involved, as well as a colmn for storing data to be added to each person's total.

Thanks in advance! 

William 

[rachaelnelson]

Create a calculated column as follows:

CollectedCalculation = IF(DailyCashReport[TookOnBehalfOf]<>"",DailyCashReport[Total]*.8,DailyCashReport[Total])

[fodelement]

Hello,

Thank you for your response. This successfully removes the 80% from the total only if the field has a name in it. So I made a new column that calculates the difference between the two so I can apply the 20% to the person who took the payment.

So thank you for that!

The question that remains now is, how can I make power BI apply the 80% removed (648.64) to the person's name that is in the column (took on behalf of)?

[rachaelnelson]

Can you send me a few rows of your dataset that is being used?  Are you trying to create a matrix with this data? Or what type of visual are you trying to create?

I could see the following but this might not be what you are trying to accomplish.

Rep           Total Collected for Self      Total Collected by other Rep         Total Collected for Other Rep

Name1         100% of Total                    80% of Total                                  20% of Total

Name2 ...

[fodelement]

Hello again, below is some test data I entered. 

Collections, Split Fee and Consult fee are added to get a total. Then Sharepoint will split the amount by 80% and 20% if "took on behalf" is populated. and populate the Fields "other person is gets" with 80%, and "total" with the 20%

Using your formula, I am able to get the below data. Now although the data is split correctly, I am looking for a way to apply the 80% that was calculated and apply it to the person that is in the "Took on Behalf of" field. So in the below example, we would want $80 of the "Other Person Gets" ($152) to go to Peter and the rest ($72) to go to Tokay. 

The data is going to be presented in simple bar graphs, like below just with one value not two. (which will be their total, + the total of "Other person Gets")

 I am essentially at the point, where I need to find a way to make this excel formula work in DAX.

[rachaelnelson]

I'm not sure if you need to create both of these calculated columns but if you do, here are the formulas.

OtherPersonGets = IF(DailyCashReport[TookOnBehalfOf]<>"",(DailyCashReport[Collections]+DailyCashReport[Split-Fee Payment]+DailyCashReport[Consult Fee])*.8,DailyCashReport[Collections]+DailyCashReport[Split-Fee Payment]+DailyCashReport[Consult Fee]

Total = DailyCashReport[Collections]+DailyCashReport[Split-Fee Payment]+DailyCashReport[Consult Fee]

Let me know if it works. I didn't enter the dataset into my BI.

[fodelement]

Good Morning,

Thank you so much for all your help, I do appreciate it.

Sharepoint already calculates those fields for me.

The end result will be a bar graph for each user. It will only have one value. That value will be:

 "Total" + "Other Person Gets"

The issue I cannot figure out is, "Other person gets" needs to be only if the "took on behalf of" = the person.

So for each instance that "took on behalf of" is = to "William" SUM those and then add them to "Total" to get a single value.

The total amount they took in for themselves, and the total amount other people took in for them. (The 80% calculation that you helped with already)

Sorry if I am not being clear, I am just flustered with all of this. 🙂

This is as simple as I can make it.

(Using excel that is)

[v-huizhn-msft]

Hi @fodelement,

Please add an index column in Power Query Edit by clicking "add column"->index column, please see following screenshot.



Then create a calculated column using the formula.

Column = IF(Table3[Person]= LOOKUPVALUE(Table3[Took on behalf],Table3[Index],Table3[Index]-1),LOOKUPVALUE(Table3[They get],Table3[Index],Table3[Index]-1)+Table3[I get],Table3[I get])

You will get the expected result.



Finally, create a bar chaart using the new column as follows.



Best Regards,
Angelia

[fodelement]

Angelia, thank you very much, this is as close as I have been to getting this to work.

TEST = IF('Daily Cash Report'[Author.FieldValuesAsText.Title]= LOOKUPVALUE('Daily Cash Report'[Took on Behalf_x0020.Title],'Daily Cash Report'[Index],'Daily Cash Report'[Index]-1),LOOKUPVALUE('Daily Cash Report'[Other Person Gets],'Daily Cash Report'[Index],'Daily Cash Report'[Index]-1)+'Daily Cash Report'[Total],'Daily Cash Report'[Total])

My only issue now, (which I hope is not a big one) is that when the person enters more than one entry, it stops adding "Other Person Gets".

Also, if the person did not create an entry, and their name yet to apear on the 'Daily Cash Report'[Author.FieldValuesAsText.Title] column, they do not appear.

Not sure if there is anything we can do to make that work.

I am thinking using excel to pull the connection from sharepoint, do the math, and then have power BI import it from the spreadsheet may be the only way to get this to work.

[v-huizhn-msft · Accepted solution]

Hi @fodelement,

Please create a new table by clicking New table under Modeling on Home page. Then type the formula to create a new table

NewTable=UNION(SELECTCOLUMNS(Table,"Name",Table[CreatedBy],"Value",table[Total]),SELECTCOLUMNS(FILTER(Table, Table[Index]=CALCULATE(MIN(Table[Index]),ALLEXCEPT(Table,Table[Took on Behalf of]))),"Name",Table[Took on Behalf of],"Value",Table[OtherPersonGets]))

Then add the Name as Axis, the sum(value) as value fields.

Best Regards,
Angelia

[fodelement]

Hello again @v-huizhn-msft

I started to test this with real data and found an issue. So here is the code.

NewTable = UNION(SELECTCOLUMNS('Daily Cash Report',"Name",'Daily Cash Report'[Author.FieldValuesAsText.Title], "office", 'Daily Cash Report'[Office.Office], "date", 'Daily Cash Report'[Date], "Value",'Daily Cash Report'[Total]),SELECTCOLUMNS(FILTER('Daily Cash Report', 'Daily Cash Report'[Index]=CALCULATE(MIN('Daily Cash Report'[Index]),ALLEXCEPT('Daily Cash Report','Daily Cash Report'[Took on Behalf_x0020.Title]))),"Name",'Daily Cash Report'[Took on Behalf_x0020.Title], "office", 'Daily Cash Report'[Office.Office], "date", 'Daily Cash Report'[Date], "Value",'Daily Cash Report'[Other Person Gets]))

Here is the Sharepoint Data

Sharepoint

Here is the "New Table" that you had me create.

Power Bi "NewTable"

Now, it successfully pulls and applies the correct amount (80%) that the person deserves, and it even puts it on the graph as I wanted. The issue is, if the person's name shows up twice, (such as the sharepoint data above), it only puts the first entry from the "took on behalf" column.

Below is the data being pulled into sharepoint, or "Daily cash Report" in the code.

If you notice in "New Table", it does however show the creators name multiple times. So I just need it to do the same thing for the "took on behlaf" of person as well.

[fodelement]

I think I am just consued about how the 

UNION(SELECTCOLUMNS

code works.  

Everytime I try to make changes, it ends up getting worse. 

[v-huizhn-msft]

Hi @fodelement,

SELECTCOLUMNS function returns a table including a column which satisfy the expression. UNION creates a join table of a pair of tables.

Best Regards,
Angelia

[fodelement]

@v-huizhn-msft , as always thank you for your insite. So in the code I posted, do you have an idea why it that's preventing the other person's name to be added to the column more than once?

In reality, if the other person's name is not added to the list again but their total is increased that'll be just fine. But in its current state once someone is added to the column via "taken on behalf of", they are not added again and their total dollar amount does not increase.

[fodelement]

Hello all!!

I still need help on this, if someone can take a look for me. 🙂

So here is a little bump.

Thanks!