Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all
I am looking for some help to figure out the DAX required to filter a column by another column. I have included some data below to illustrate the problem.
In this example there are 3 companies (Alpha, Bravo, and Charlie). Alpha and Bravo are both to be compared against Bravo's sales (Reference Company). What i am trying to figure is how to calculate Reference Company Sales in the output.
I though the DAX might go as follows but it doenst work as A_Table[Company] doesnt return all rows, only the current row.
Reference Company Sales = CALCULATE(SUM[Sales],filter(A_Table, A_Table[Company]=[Reference Company]))
The example is below:
Thanks in advance for any help you are able to provide.
Solved! Go to Solution.
Hi @DaneHowarth
Here is a sample file with the solution https://www.dropbox.com/t/T2GfvtNkOp8FqBl4
Reference Company Sales =
CALCULATE (
SUM ( A_Table[Sales] ),
REMOVEFILTERS ( A_Table ),
TREATAS ( VALUES ( A_Table[Reference Company] ), A_Table[Company] )
)
Hi @DaneHowarth
I just noticed that my original solution deliveres correct results but wrong total. Here is the solution with the correct total https://www.dropbox.com/t/z9kBn81LzoPEG7Ex
Reference Company Sales =
SUMX (
SUMMARIZE ( A_Table, A_Table[Company], A_Table[Reference Company] ),
CALCULATE (
CALCULATE (
SUM ( A_Table[Sales] ),
REMOVEFILTERS(),
TREATAS ( VALUES ( A_Table[Reference Company] ), A_Table[Company])
)
)
)
Hi @DaneHowarth
Here is a sample file with the solution https://www.dropbox.com/t/T2GfvtNkOp8FqBl4
Reference Company Sales =
CALCULATE (
SUM ( A_Table[Sales] ),
REMOVEFILTERS ( A_Table ),
TREATAS ( VALUES ( A_Table[Reference Company] ), A_Table[Company] )
)
@DaneHowarth , Create a measure like
Reference Company Sales = CALCULATE(SUM(Table[Sales]),filter(allselected(A_Table), A_Table[Company]= max(Table[Reference Company])))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |