Measure Calculation

Question

Hello,&nbsp;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:&nbsp;&nbsp; &nbsp; &nbsp; Avenue&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;Location&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;Online&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;Location A&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;Online&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;Location B&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;In-Store&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;Location A&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;In-Store&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp;Location B&nbsp; &nbsp; &nbsp;&nbsp;Then my Sales table looks something like:&nbsp;&nbsp; &nbsp; &nbsp;OrderID&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;Avenue&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;Location&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;ProductID&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;Transaction Ammt&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;12345&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;Online&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;Location A&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;A1&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;$500&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;45678&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;Online&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;Location B&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;B1&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;$250&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;78912&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;In-Store&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;Location A&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;A1&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;$325&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;15975&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;In-Store&nbsp; &nbsp; &nbsp;Location B&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;C1&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;$500&nbsp; &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;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:&nbsp;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.&nbsp;The totals that should be in each of the avenue totals are:&nbsp;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])))&nbsp;Whenever I try and add anything to the formula, it gives me something further away from what I am looking for.&nbsp;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!

vicky_ · Answer

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])))

ashish_mathur · Answer

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

nj81858 · Answer

Ashish_Mathur&nbsp;The expected result with the dummy data for Product A1 would be:&nbsp;&nbsp; &nbsp; &nbsp;Avenue&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;Location&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;Amount&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;Total Amount&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;% of Total&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;Online&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;Location A&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;$500&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;$500&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;100%&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp;Location B&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;$0&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;$500&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;0%&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;In-Store&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;Location A&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;$325&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;$325&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;100%&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp;Location B&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;$0&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;$325&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;0%&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;TOTAL&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp;$825&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;$825&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;100%&nbsp; &nbsp; &nbsp;&nbsp;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 · Answer

Hi,
You may download my PBI file from here.
Hope this helps.

Forum Discussion

Measure Calculation

4 Replies

Related content

DAX Calculated Measure

DAX Measure for Ranking the valid rows and calculating the average rating

Measure or Calculated Column

Efficiently scaling YoY calculations for hundreds of measures (Calculation Groups vs. Alternatives)

Measure to calculate

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

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

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%