Forum Discussion
Reduce by percent if column value equals something.
Hi fodelement,
Please create a new table by clicking New table under Modeling on Home page. Then type the formula to create a new tableNewTable=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
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)?
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 ...
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.
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.
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)