I am going to try my best to articulate what it is I am struggling with. I have a straight table that shows credits and non credits what I am trying to do is create a formula that would add up the credits for a total $ as well as the same for non credits and then present a third dimension as a percentage of the two.
For example: % of total credits compared to total sales $ "Basically credits as a percentage of total sales." The problem is that the both the credits and sales are in the same table field. I have been racking my brain on this one and I am sure it is something simple.
What I need to do is create three additional expressions, one that shows the total sales excluding the credits, another showing the credits only, and a third that calculates the percentage between the two.
Essentially what I need to know is the % of credits compared to total sales across multiple dimensions.,
What I have is a single dataset showing both positive and negative numbers, what I am trying to accomplish is the creation of two expressions. One showing all of the positive numbers and the second showing the negative numbers. I have been playing with the syntax but can’t seem to get it right.
If you have the 2 "credit reason" columns in your chart, then you'd need to aggregate away from these columns in order to be able to present a single view of credits and non-credits per customer/item. You would accomplish that by using the "total" keyword in your sum. Not exactly sure which expression you had in mind, but try something like this:
Positives: sum({<Sales={">0"}>} total <[Customer Name],[Item Number]> Sales)
Negatives: sum({<Sales={"<0"}>} total <[Customer Name],[Item Number]> Sales)
Replace "Sales" with the number field you used to create "Product_Only $"
Alternatively, if you don't have the "credit reason" columns, you can get rid of "total <[Customer Name],[Item Number]"
