Measure Calculation

[NJ81858]

Hello,

I am trying to calculate the percentage of total sales of a particular product through different puchasing avenues, and different locations for each month. I have a table that is set up for each combination of avenue and location that looks something like:

Avenue	Location
Online	Location A
Online	Location B
In-Store	Location A
In-Store	Location B

Then my Sales table looks something like:

OrderID	Avenue	Location	ProductID	Transaction Ammt
12345	Online	Location A	A1	$500
45678	Online	Location B	B1	$250
78912	In-Store	Location A	A1	$325
15975	In-Store	Location B	C1	$500

In my visual that I want to display, I am calculating the percentage of total sales that are at each Avenue and Location for a particular product. My Matrix currently looks like this:

Currently in there I have "% numerator", which is the total transaction amount for each level, "% denominator", which is supposed to be the total dollar amount within each Avenue, and then "TEST %", which is the numerator divided by the denominator measure.

The totals that should be in each of the avenue totals are:

So what I have currently is just taking the sum of everything, rather than finding the sum within each category. My measure for the denominator currently is:

(CALCULATE(SUM('Sales'[Transaction Ammt]), ALLEXCEPT('Sales','Sales'[Date])))

Whenever I try and add anything to the formula, it gives me something further away from what I am looking for.

I am looking for every single one of the values in my matrix to equal the value of the total, so for the first value of Avenue, the total would be 913,070 in each space, for the second value, it would be 38,220 in each space, and then 252,133 for each space in the third value of Avenue. I am just struggling what to change with my measure to get those values for each space. Thank you in advance!

[Ashish_Mathur]

Hi,

Show the expected result on the dummy sales dataset that you have shared.

[NJ81858]

@Ashish_Mathur The expected result with the dummy data for Product A1 would be:

Avenue	Location	Amount	Total Amount	% of Total
Online	Location A	$500	$500	100%
	Location B	$0	$500	0%
In-Store	Location A	$325	$325	100%
	Location B	$0	$325	0%
TOTAL		$825	$825	100%

Note that this visual is a matrix, which is why the second value for each avenue is empty. Also note that the Avenue and Location come from the first table of all possible combinations, otherwise the combinations that yield $0 will not show, such as the combination of Online and Location B.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[vicky_]

The measure you have is removing the filter for avenue, which you want to keep right?

One solution is to simply add another argument to the ALLEXCEPT Filter: 

(CALCULATE(SUM('Sales'[Transaction Ammt]), ALLEXCEPT('Sales','Sales'[Date], 'Sales'[Avenue])))