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,
In the below tables I am trying to create a measure which does a calculation as shown in the Output table.
For a given Plant-Business-Month, sum of each type of heirarchy is divided by the 'sum of Volume of that month.
e.g for Jan-2020 Sales Revenue = (20+30+40)/1000 = 9%
In my report I have slicers set on Plant, Business and Heirarchy.
In the above scenario to calculate this, I want the denominator to ignore the filter context on Heirarchy coming from outside. And I am trying to plot the Months in a line chart.
I want to be able to select Heirarchy on slicers and line chart shows me month over month rate.
Hope this makes sense. Your help is highly appreciated.
MONTH | PLANT | BUSINESS | ATTRIBUTE | VALUE | |
Jan-20 | A | Xyz | Vol | 1000 | |
Jan-20 | A | Xyz | SS | 20 | |
Jan-20 | A | Xyz | SS | 30 | |
Jan-20 | A | Xyz | MM | 40 | |
Jan-20 | A | Xyz | Var | 50 | |
Feb-20 | A | Xyz | V | 2000 | |
Feb-20 | A | Xyz | SS | 30 | |
Feb-20 | A | Xyz | MM | 40 | |
HEIRARCHY | ATTRIBUTE | ||||
Volume | Vol | ||||
Volume | V | ||||
Sales Revenue | SS | ||||
Sales Revenue | MM | ||||
Var Margin | Var | ||||
OUTPUT | |||||
HEIRARCHY | MONTH | RATE | |||
Sales Revenue | Jan-20 | 9% | |||
Var margin | Jan-20 | 5% | |||
Sales REvenue | Feb-20 | 4% |
Hi @Anonymous ,
Try to create four measures.
Measure =
CALCULATE(
SUM(Sheet1[VALUE]),
FILTER(
Sheet2,
Sheet2[HEIRARCHY] = SELECTEDVALUE(Sheet2[HEIRARCHY]) && Sheet2[HEIRARCHY] <> "Volume"
),
FILTER(
Sheet1,
Sheet1[MONTH] = SELECTEDVALUE(Sheet1[MONTH])
)
)
Measure 2 =
VAR x =
CALCULATE(
SUM(Sheet1[VALUE]),
FILTER(
Sheet2,
Sheet2[HEIRARCHY] = SELECTEDVALUE(Sheet2[HEIRARCHY]) && Sheet2[HEIRARCHY] = "Volume"
),
FILTER(
Sheet1,
Sheet1[MONTH]= SELECTEDVALUE(Sheet1[MONTH])
)
)
RETURN
x
Measure 3 =
VAR x =
SUMX(
FILTER(
ALL(Sheet1),
Sheet1[MONTH] = SELECTEDVALUE(Sheet1[MONTH])
),
[Measure 2]
)
RETURN
x
Measure 4 =
DIVIDE(
[Measure], [Measure 3],
BLANK()
)
(Note: Do not combine Measure2 and Measure3 into one formula, as this may cause problems.)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-lionel-msft ,
Thank you for the response.
I think the issue here is that Measure 3 calculates the total "Volume" for a given date irrespective of outside filter say a Plant or Business. I want the meausre to ignore only Heirarchy filter but keep the Plant and Business filters.
Any thoughts?
Regards,
Atul
Hi @Anonymous ,
Or like this?
Measure 3 =
VAR x =
SUMX(
FILTER(
ALL(Sheet1),
Sheet1[MONTH] = SELECTEDVALUE(Sheet1[MONTH]) &&
Sheet1[Plant] = SELECTEDVALUE(Sheet1[Plant]) &&
Sheet1[Business] = SELECTEDVALUE(Sheet1[Business])
),
[Measure 2]
)
RETURN
x
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
That did it! Thanks.
@Anonymous , Also refer : https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept/
Well, to ignore context, you would use REMOTEFILTERS or possibly ALLEXCEPT. Sometimes you need to start with ALL and then use FILTER to put the filters you want back in place. Depends.
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |