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.
Hello community!
First time posting here, so please do not hesitate to ask if you need more details.
The Report context
The basic premise of the report is that the user can use a few slicers to select a portion of the company's products (Category, Sub-Category, Segment and Trend) and a slicer for the time period (usually current week -1 and the same period the previous year)
The user then gets 2 tables:
The Problem
In my Top10 table, I want to add a contribution% column which would do the following calculation:
Sales of item / Sales of total selection
For example (see capture of report): Sales of item A (137,578$) / Sales of the selection for 2023(1,851,206$)
No matter what way I turn the formula, I cannot seem to be able to avoid the visual level filter on the Ranking Measure, so my denominator tops out at 539,128 (total of top 10 for 2023) instead of getting the 1,851,206 (total of the selection for 2023)
Also important to note:
My model is in Live Connect - so I cannot create a column in the model
From what I've gathered, the ideal is to create a virtual table as a variable and then run a SUMX on it.
Something like this:
DENOMINATOR - Sales of selection =
VAR tabletopstyles =
FILTER (
KEEPFILTERS (
SUMMARIZE (
ALL ( Items ),
Items[Style Image],
"Sales", [Sales],
"Rank", [_Top by Sales]
)
),
NOT ( ISBLANK ( [_Top by Sales] ) )
)
RETURN
SUMX ( tabletopstyles, [Sales] )
But after testing:
I cannot use REMOVEFILTERS as it will not take an expression as a second argument
Using ALL takes away all my slicer filters AND my visual level measure filter
Using ALLSELECTED keeps my slicer filters AND my visual level measure filter....HELP!
Last note: I've been able to do the opposite, which is bring my Top10 sales total back into my Total for all selection table at the top. But that table does not have a visual level filter. The formula was the following
Ideas on alternatives are also welcome!
@m4emily , we will not be able to remove the measure filter , do better create measures with filter
like you can have TOPN measures
calculate([Net Sales PY], TOPN(2, allselected('Address'[Country ISO]), [Net Sales PY],desc), values('Address'[Country ISO]))
calculate([Net Sales], TOPN(2, allselected('Address'[Country ISO]), [Net Sales PY],desc), values('Address'[Country ISO]))
Both are TOP 2 on prior year sales
TOPN: https://youtu.be/QIVEFp-QiOk
TOPN with Numeric Parameter -https://youtu.be/cN8AO3_vmlY?t=26448
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 |
---|---|
42 | |
21 | |
21 | |
14 | |
14 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |