Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Petanek333
Helper III
Helper III

Sum of selected field parameter values (in a card visual)

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. 

Petanek333_0-1672487830972.png

 

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

Sample file 

 

1 ACCEPTED SOLUTION
v-jianboli-msft
Community Support
Community Support

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:

vjianbolimsft_0-1672726776043.png

vjianbolimsft_1-1672726790254.png

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.

View solution in original post

4 REPLIES 4
v-jianboli-msft
Community Support
Community Support

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:

vjianbolimsft_0-1672726776043.png

vjianbolimsft_1-1672726790254.png

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

MAwwad
Super User
Super User

 

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:

  1. First, create a measure that uses the SUMX function to sum up the values of the "WoW Customer A", "WoW Customer B", and "WoW Customer C" measures based on the selected customer in the field parameter. The SUMX function takes a table and an expression as arguments, and returns the sum of the values in the expression for each row in the table. For example:

     


    Total Sales = SUMX ( VALUES ( '_Measures WoW'[Customer] ), SWITCH ( '_Measures WoW'[Customer], "Customer A", '_Measures WoW'[WoW Customer A], "Customer B", '_Measures WoW'[WoW Customer B], "Customer C", '_Measures WoW'[WoW Customer C], 0 ) )

     

     
    1. 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.

    2. 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:

       


      Total Sales = IF ( VALUES ( '_Measures WoW'[

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.