Comparing a totaled value with a related field in another table

[Anonymous]

Objective: I want to summarize daily detailed data by month and then compare the totaled results to a value in a related record in a dimension table -- and then based on the comparison filter out certain results.  To state this more clearly, I want to summarize Gross Profit from by daily detailed table by Customer and then compare the summed Gross Profit to a GP Threshold value stored in a Customers dimension table, and lastly filter out those Total Gross Profit that are greater than the customer's GP Threshold.  I would like to accomplish the above without having to create a summarized table in PowerQuery/M as my data set is very large and takes a very long time to load.  Instead I would prefer to only load the detail data and then accomplish the above through DAX formulas.

Problem: I can visually get the summed totals in a Matrix or a Table visual and also side by side display the dimension value from the related dimension table.  I can do this whether putting the direct fields from the detailed table into the visual, or by creating measures the SUMX the fields. However, I can not figure out how to do the comparison (the IF) and then the filtering.

Example data is below.

Any suggestions on how to approach this without summarizing first in PowerQuery?

Thanks,

Example Tables:

Dimensions -

Customer Table

Cust No	Cust
A	ABC
B	BCD
C	CDE
D	DEF

Thresholds 

Cust No	GP Threshold
A	10
C	30

Date Reference

Date	Year	MonthOfYear	Month Name
1/1/2020	2020	1	January
1/2/2020	2020	1	January
1/3/2020	2020	1	January
2/1/2020	2020	2	February
2/15/2020	2020	2	February
3/1/2020	2020	3	March
3/2/2020	2020	3	March
3/3/2020	2020	3	March
3/4/2020	2020	3	March
3/5/2020	2020	3	March
3/16/2020	2020	3	March
4/1/2020	2020	4	April
4/2/2020	2020	4	April
4/3/2020	2020	4	April
4/4/2020	2020	4	April
4/5/2020	2020	4	April
4/15/2020	2020	4	April
4/16/2020	2020	4	April

Transactions-

Daily Transactions

Cust No	Date	Rev	Gross Profit
A	1/1/2020	100	11
A	1/2/2020	100	8
A	2/1/2020	100	9
A	2/15/2020	100	13
A	3/1/2020	100	12
A	4/2/2020	100	4
B	1/3/2020	200	18
C	3/3/2020	300	35
C	3/4/2020	300	26
C	4/5/2020	300	25
D	3/16/2020	400	42
D	4/15/2020	400	43
D	4/16/2020	400	35

Table output

Matrix Output

[MFelix]

Hi @Anonymous ,

I have prepared a mockup file, however I have some doubts about what is the result you need.

To what I can understand you want to show gross profit for values below the treshold correct?

On the file attach I have created the following measure:

Measure_tofilter_gross_profit =
IF (
    CALCULATE (
        SUM ( 'Thresholds'[GP Threshold] );
        FILTER (
            ALL ( 'Thresholds'[GP Threshold] );
            'Thresholds'[GP Threshold] = SELECTEDVALUE ( 'Thresholds'[GP Threshold] )
        )
    )
        - SUM ( 'Daily Transactions'[Gross Profit] ) < 0;
    1;
    BLANK ()
)

Basically if the GP Threshold is greater or equal to the sum ogf the gross profit I return 1 otherwise is blank.

Can you check if this is correct. if this is correct and based on this you want to make the gross profit blank based on this comparision please tell me and we can adjust the calculations.

Another question is regarding regarding the totals you only want to show the sum of the ones that are visible?

[Anonymous · Accepted solution]

Thanks Miguel!

Yes this did work for both comparing a GP target as well as, with a few modifications, a GP percentage target.

I am still coming up to speed with when to use Calculate and the Context perspectives.  I'll study through this and tyr to figure out why your solution worked.

On thing that confused me was the need to Sum the values in the GP Threshold dimension table when there is only a single matching row (one:many relationship) instead of just being able to simply reference that single row/value.  If you have a simple explanation or insight that may help me.

I'll mark this as solved.

Thanks,