Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi,
I have a simple field parameter like this:
Selling Price = {
("Customer A", NAMEOF('_Measures WoW'[WoW Customer A]), 1),
("Customer B", NAMEOF('_Measures WoW'[WoW Customer B]), 2),
("Customer C", NAMEOF('_Measures WoW'[WoW Customer C]), 3),
("Nothing", NAMEOF('Blank table'[Nothing]), 0)
}
Measure "WoW Customer A (or B or C)" is a measure creating a virtual table which filters only customer A sales and uses sumx function to add up the number of sales of various products and multiply them by their selling price.
"Nothing" is a blank table because I need to have an option to display no values. So far I have used this field parameter to show and compare columns in a column chart.
If I select Customer A, B and C I have 3 columns, that is OK. But my boss now wants to see the sum of all the selected customers, the value of 1.220.689 in this case. If I put the field parameter measure into a card visual, it only shows a value if one customer is selected. In case more than 1 customer is selected, it still shows the value of the first customer 53.208.
Is there any way how to show the total of selected customers in one visual using the measures and field parameter I have?
I found a workaround using a stacked bar chart, setting all the colors of the bars to white and setting the height and width of the visual so that it only show the total number of stacked bars. But there surely is a way how to display it in a single measure.
EDIT: I added a sample file
Solved! Go to Solution.
Hi @Petanek333 ,
Please try:
Measure =
VAR _a =
SELECTCOLUMNS ( 'Purchasing Value', "Name", [Purchasing Value] )
RETURN
SUMX (
_a,
SWITCH (
[Name],
"Purchasing Value A", [Purchasing Value A],
"Purchasing Value B", [Purchasing Value B],
"Purchasing Value C", [Purchasing Value C],
0
)
)
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Petanek333 ,
Please try:
Measure =
VAR _a =
SELECTCOLUMNS ( 'Purchasing Value', "Name", [Purchasing Value] )
RETURN
SUMX (
_a,
SWITCH (
[Name],
"Purchasing Value A", [Purchasing Value A],
"Purchasing Value B", [Purchasing Value B],
"Purchasing Value C", [Purchasing Value C],
0
)
)
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Absolutely fantastic! Thank you
To show the total of selected customers in a single visual using the measures and field parameter you have described, you can use a combination of the SUMX and SWITCH functions in Power BI.
Here is an example of how you can create a measure that shows the total of selected customers:
Next, create a card visual and add the Total Sales measure to it. This will show the total sales for the selected customers in the field parameter.
If you want to display a blank value when no customers are selected, you can use the IF function to check the value of the field parameter. If the value of the field parameter is "Nothing", you can return a blank value. For example:
Hi @MAwwad ,
I created a sample data in order to solve this.
I need the individual channels to be calculated by the measures (Selling Value A, Selling Value B, ...) because I need to display them as individual bars in the chart below. This is the only method I've come up with for displaying it this way.
But then I have a problem displaying the total for the Selling Value or Purchasing value anyway. I can work this around with a stacked bar chart, but ideally I'd like to display it in a card visual and not have it display errors when selecting Nothing in the slicer.
Sample data: Data
Can you help me with this?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
141 | |
121 | |
112 | |
59 | |
58 |