Measure that calculates the filtered total with relationship between tables

[Anonymous]

Hi there, I'm quite new to PowerBI and I'm having an issue calculating a percentage inside a measure, and I've identified the problem, in the total value. 

I've two tables:
TABLE: SUMMARY

Category	Product	Year	Value
A	Apples	2018	150
A	Bananas	2018	200
B	Strawberries	2018	250
B	Cherries	2018	300
C	Melons	2018	350
C	Watermelons	2018	400
A	Apples	2019	450
A	Bananas	2019	500
B	Strawberries	2019	550
B	Cherries	2019	600
C	Melons	2019	650
C	Watermelons	2019	700

TABLE: GENERAL

Category	Year	Total value
A	2018	1500
B	2018	2000
C	2018	2500
A	2019	3000
B	2019	3500
C	2019	4000

I built a relationship many to many between tables using the Category variable.

The column GENERAL[Total value] contains the total value for category A, knowing that for table SUMMARY it's not mandatory to download in the database all products of category A => total value =\= SUM(SUMMARY[Value] with filter for category A.

I've a Slicer on Product level where for example I want to select Apples, Bananas and Strawberries only for Year 2018 and obtain a table as it follows:

Product	Numerator	Denominator
Apples	150	3500
Bananas	200	3500
Strawberries	250	3500

Where the Numerator is only the value of the Product in the table SUMMARY, instead the Denominator is the sum of the Total Value of the corresponding categories for filtered products (in this case I've products from category A and B, so is the some of Total Value of table GENERAL for categories A and B => then I can obtain a % as DIVIDE(Numerator; Denominator)

At the moment via measures, what I've achieved is the following:

Product	Numerator	Denominator
Apples	150	1500
Bananas	200	1500
Strawberries	250	2000

So I can't figure it out how to obtain a general Denominator that changes with the Products selected on the filter (for instance if I select in the slicer Products from Categoris B and C the Denominator should be 4500 (I'm filtering everything on year 2018) and so on (if I select a product or more for every category, the Denominator should be 6000)

I hope I've been clear enough, thanks in advance for helping me! 

[v-shex-msft · Accepted solution]

Hi @Anonymous ,

I'd like to suggest you create a new table extract category values from two tables and use it as a bridge to link to tables instead of using many to many relationships.

Bridge =
DISTINCT (
    UNION ( VALUES ( SUMMARY[Category] ), VALUES ( GENERAL[Category] ) )
)

DAX Puzzle: Multiple many-to-many relationships 

These value will auto summarize based on their relationship:

Regards,

Xiaoxin Sheng

[edhans]

I'm not clear on where you are getting your values. For example, you said if you select a slicer for every category, the denominator should be 6,000, but the detail table only has 5,100 total.

I'd forget the summary table, and especially any Many - to - Many relationship. Those are tricky and should only be used in very specific circumstances.

Try the below measure. It may not be what you want, but would be a matter of changing the REMOVEFILTERS() funtion's parameters, or swapping it out with ALLEXCEPT() or ALLSELECTED() depending on needs.

Percent of Total = 
VAR Denominator = 
    CALCULATE(
        [Total Value],
        REMOVEFILTERS(Sales)
    )
VAR Numerator = SUM(Sales[Value])
RETURN
DIVIDE(
    Numerator,
    Denominator,
    0
)

take a look at that and see if that helps. You should almost always (99.9999% of the time) use a detail table to create totals by removing filters, not by creating a new table to try and relate to it.

Post back and we'll see if we can tweak it to your exact needs once you've looked at it.

[Anonymous]

Hi edhans thanks for the reply!! 

The thing of summary and general table is not something I can forget because I've a big dashboard where both are needed (the summary is a focus on product level, the general on category, because for the summary you download only part of products for every category, like top x% products for value). 

The 5100 is the total of value in summary table, but as I mentioned it is not exhaustive of all products that could be inserted in the database => the general table instead contains the total value for each category (let's assume that category A has for year 2018 3 products: Apples with a value of 150, Bananas with a value of 200 and Cheese with a value of 1150 => the sum is 1500, so in the general table you see 1500 as a total for category A, but in the summary table, you don't have the value for cheese because in my vba code where I create the database is not mandatory to download data for all the products).

Let me know if I've been a little bit clearer, I know that is really tricky 😞 

PS: I've tried to use the measure you indicated, but I don't understand why, if I type CALCULATE([Name of Column]) it gives me an error saying that value for [Name of Column] cannot be determined and also when it comes to filter, I can use all the functions that I know like ALL(), ALLEXCEPT(), ALLSELECTED(), but I can't use the REMOVEFILTERS(), it doesn't even appear in the suggestion when I start typing; is it maybe I'm using an old version of Power BI?

[edhans]

Ok. By not having the summary table be the same as the detail table (records missing) it raises issues. You may need to study this document and focus on the different granularity section (higher grain facts section). It is one of the few times to use a many-to-many relationship, but your life would definitely be easier if the detail table had all of the records and the totals were just math.

As for CALCULATE([column name]) won't work. Calculate either needs a measure in the expression or a fully qualified table[column] name.

I think REMOVEFILTERS() was added a few months ago - July/August 2019 perhaps? You can use ALL() instead. REMOVEFILTERS is an alias I prefer to use as that is what you are doing within CALCULATE whereas ALL() is capable of much more, but for your needs ALL() will work. Or, you are typing REMOVEFILTERS() in the wrong place. It can only be used as a filter in a CALCULATE() or CALCULATETABLE() function. Doesn't work by itself, within FILTER() or any other function.