Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi, I am currently attempting to process a huge amount of data on PowerBI that Excel cannot handle.
First let me share some background.
------------------------------------------------------------------------------------------------------------------------------------
I am using a query that has as query elements "Sales", "Gross profit amount", and "Deal number".
I want to analyze this data by aggregating by case number, so I have set up the following in a visual (scatter plot).
Value... Deal number
X axis... Sales
Y axis... Gross Profit margin
Here, the "Gross Profit margin" is an added measure, and the formula is "Gross Profit amount"/"Sales".
The measure is used because if it were added in a query, the "Gross Profit margin" would be simply averaged when aggregating by case number.
(A situation I wish I could have avoided:
If there are two data sets for the same deal number with "Gross Profit margins" of 20% and 30%, the "Gross Profit margin" would be 25% without taking into account the amount of sales, etc.)
------------------------------------------------------------------------------------------------------------------------------------
And here comes the problem.
I want to check the change in total Sales and overall Gross Profit margin by extracting only the "gross profit margin" of 0% or less from this aggregated scatter plot.
I have tried both of the following two methods, but without success.
1. visually filter and observe the changes in the cards.
I set the total number of sales, gross profit margin, and case number on the card.
However, the cards did not change in conjunction with the mere filtering of the visual.
2. using a slicer
Slicer is set to sales and gross profit margin.
Sales slicer was set to the original query level layer, not the aggregated layer
The gross margin slicer could not be set because the gross margin was a major factor to begin with.
It would be easier to use a pivot table in Excel, but due to the number of data, Excel cannot be used.
I would be happy if someone could give me some advice.
Solved! Go to Solution.
@ShotaMizuno , Aggregated value can be filtered using some grouping
example
sumx(filter(summarize(Sales, Geo[City], "_1", [Sales Measure]) ,[_1] > 10000),[_1])
In place of 10000 you can use what if the parameter value
https://docs.microsoft.com/en-us/power-bi/desktop-what-if
@ShotaMizuno , Aggregated value can be filtered using some grouping
example
sumx(filter(summarize(Sales, Geo[City], "_1", [Sales Measure]) ,[_1] > 10000),[_1])
In place of 10000 you can use what if the parameter value
https://docs.microsoft.com/en-us/power-bi/desktop-what-if
User | Count |
---|---|
93 | |
86 | |
68 | |
62 | |
58 |
User | Count |
---|---|
149 | |
113 | |
95 | |
80 | |
72 |