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,
First of all, I am quite a newbie with creating Dax measures, so please bear over with my shortcomings of knowledge (and probably also ability to explain the actual issue).
Primarily, I am trying to visualize (line chart) the development of market share of a selected group of products, the base being sales of all products.
Mockup of all data (no column for :
table1
Product | Type | Data level | Size | Sales | Share of Grand Total Sales |
Alfa | A | Geography | S | 10 | 0,10 |
Bravo | B | Geography | M | 15 | 0,15 |
Charlie | B | Chain | L | 25 | 0,25 |
Delta | C | Chain | M | 40 | 0,40 |
Echo | B | Chain | M | 10 | 0,10 |
Grand Total | 100 | 1 |
So the key is: After slicer operations, the Sales of each product is to be measured against the "all" salesa in the table.
Desired result of measure (* mark slicer selections)
Product | Type* | Data level* | Size | Sales | Share of Grand Total Sales |
Charlie | B | Chain | L | 25 | 0,25 |
Echo | B | Chain | M | 10 | 0,1 |
Total | 0,35 |
My secondary challenge, due to the data structure, I need the Dax measure to control which of the slicers that should influence the base (and others not). Is this possible?
I would really appreciate any input.
Regards,
depple
Solved! Go to Solution.
@depple
You can create the following measure to get the desired results:
Share of Grand Total Sales =
DIVIDE(
SUM(SalesData[Sales]),
CALCULATE(
SUM(SalesData[Sales]),
ALL(SalesData)
)
)
If you need to control the clearing of filters then you can selectively choose with ALLEXCEPT as follows:
Share of Grand Total Sales =
DIVIDE(
SUM(SalesData[Sales]),
CALCULATE(
SUM(SalesData[Sales]),
ALLEXCEPT(SalesData, SalesData[Type])
)
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@depple
You can create the following measure to get the desired results:
Share of Grand Total Sales =
DIVIDE(
SUM(SalesData[Sales]),
CALCULATE(
SUM(SalesData[Sales]),
ALL(SalesData)
)
)
If you need to control the clearing of filters then you can selectively choose with ALLEXCEPT as follows:
Share of Grand Total Sales =
DIVIDE(
SUM(SalesData[Sales]),
CALCULATE(
SUM(SalesData[Sales]),
ALLEXCEPT(SalesData, SalesData[Type])
)
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi @Fowmy
That worked perfectly!
Ended up with quite a lot of ALLEXCEPT-columns, maybe there should be a ALLSELECTEDEXCEPT function 🙂
Thank you very much for your help, that really pushed my report forward.
/depple
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 |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |