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.
I need to create a measure that returns the "Qty" from column "Total Sold" for the column "Full Paid", "Half Paid" and "Free", but I cannot ignore the slicers on the page or the Stores (on lines). I need to create something like the following measure, but it doensn't work by the way...
Measure that I need =
CALCULATE(
SUM(BI_Sales[Qty]);
FILTER(
ALL(BI_Sales[Atribute]);
BI_Sales[Atribute] = "Total Sold"
)
)
Here a matrix for example:
Here a sample to create the previous matrix:
Store | Atribute | Qty | OrderAtribute |
Store 1 | Total Sold | 100 | 1 |
Store 1 | Half Paid | 40 | 3 |
Store 1 | Free | 10 | 4 |
Store 1 | Full Paid | 50 | 2 |
Store 2 | Total Sold | 200 | 1 |
Store 2 | Half Paid | 80 | 3 |
Store 2 | Free | 20 | 4 |
Store 2 | Full Paid | 100 | 2 |
Store 3 | Total Sold | 300 | 1 |
Store 3 | Half Paid | 120 | 3 |
Store 3 | Free | 30 | 4 |
Store 3 | Full Paid | 150 | 2 |
Store 4 | Total Sold | 400 | 1 |
Store 4 | Half Paid | 160 | 3 |
Store 4 | Free | 40 | 4 |
Store 4 | Full Paid | 200 | 2 |
Solved! Go to Solution.
Thanks for updating the post with your source table format 🙂 That really helped. You state you want the slicers on your page being repsected when this measure is evaluated. The measure you are looking for is this:
Sum QTY Total Sold =
CALCULATE(
SUM(BI_Sales[Qty]);
BI_Sales[Atribute] = "Total Sold"
)
)
You were very close. You don't need to give a complete new context of the datamodel to the CALCULATE statement; you want to filter in the current context this measure is evaluated in. By using FILTER(ALL(table),<exp>), you essentially throw away all filters imposed by your report slicers and filter the dataset your self.
Try putting this in a Card visual and see if that reflects the right value 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
Hi @gluizqueiroz ,
To be honest, I don't quite understand what you want. It would be better to share some sample data and expected result to us id you don't have any Confidential Information. However, if you don't want a visual be effected by a slicer, you could use Edit Interactions feature.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey @v-jayw-msft
I have updated the post to be easy to understand my question.
Sorry for my mistake when creating the post.
Thanks for updating the post with your source table format 🙂 That really helped. You state you want the slicers on your page being repsected when this measure is evaluated. The measure you are looking for is this:
Sum QTY Total Sold =
CALCULATE(
SUM(BI_Sales[Qty]);
BI_Sales[Atribute] = "Total Sold"
)
)
You were very close. You don't need to give a complete new context of the datamodel to the CALCULATE statement; you want to filter in the current context this measure is evaluated in. By using FILTER(ALL(table),<exp>), you essentially throw away all filters imposed by your report slicers and filter the dataset your self.
Try putting this in a Card visual and see if that reflects the right value 🙂
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
Can you give some sample data? I don't understand the underlying datamodel (nor can I derive it from your matrix).
Thanks!
Proud to be a Super User!
Hey @JarroVGIT
I have updated the post to be easy to understand my question.
Sorry for my mistake when creating the post.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |