Summing a Range Based on Serial Numbers

[trathman]

I have a serial number column that is based on 3 different sets of numbers. The first set (A) is four digit, the second (B) and third (C) set are 2 digits. To create the serial number I apply the following transformation:
A*10000+B*100+C = AAAABBCC
Am I able to create a measure that is:
VAR SerialStart = SerialStart (Coming from different slicer selections based on A, B, C)
VAR SerialEnd = SerialEnd (Coming from different slicer selections based on A, B', C')

CALCULATE(
SUM('BookDtl'[BookAmt]),
FILTER('BookDtl',
            'BookDtl'[Serial] >= SerialStart &&
            'BookDtl'[Serial] <= SerialEnd
           )
)

What is my error? 
 

[Anonymous · Accepted solution]

Hi @trathman ,
Thanks for Ashish_Mathur and DataNinja777 replies and efforts.

Based on your description and their suggestions, here is the sample data and MEASURE I created

To make sure  slicer works, there should be no relationship between the tables

Create a measure

Total = 
VAR SerialStart = 
    SELECTEDVALUE(SlicerTableA[ValueA]) * 10000 +
    SELECTEDVALUE(SlicerTableB[ValueB]) * 100 +
    SELECTEDVALUE(SlicerTableC[ValueC])

VAR SerialEnd = 
    SELECTEDVALUE(SlicerTableA[ValueA]) * 10000 +
    SELECTEDVALUE('SlicerTableB'''[ValueB']) * 100 +
    SELECTEDVALUE('SlicerTableC'''[ValueC'])

RETURN
CALCULATE(
    SUM(BookDtl[BookAmt]),
    FILTER(
        BookDtl,
        BookDtl[Serial] >= SerialStart &&
        BookDtl[Serial] <= SerialEnd
    )
)

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Ashish_Mathur]

Hi,

In Power Query, ensure that the data type of the Serial number column is number.  This measure should work

Measure = calculate(SUM('BookDtl'[BookAmt]),'BookDtl'[Serial]>=min('BookDtl'[Serial])&&'BookDtl'[serial]<=max('BookDtl'[Serial]))

If this does not work, then share the download link of the PBI file and show the expected result very clearly.

[DataNinja777]

Hi @trathman ,

Your error likely comes from how SerialStart and SerialEnd are being defined, as they need to be properly extracted from the slicer selections. If each part of the serial number (A, B, and C) is selected separately for both the start and end values, they should be referenced correctly. Additionally, the FILTER function must be applied to the correct table to ensure that only the relevant range is considered. Below is the corrected measure.

VAR SerialStart = 
    SELECTEDVALUE('SlicerTable'[A_Start]) * 10000 +
    SELECTEDVALUE('SlicerTable'[B_Start]) * 100 +
    SELECTEDVALUE('SlicerTable'[C_Start])

VAR SerialEnd = 
    SELECTEDVALUE('SlicerTable'[A_End]) * 10000 +
    SELECTEDVALUE('SlicerTable'[B_End]) * 100 +
    SELECTEDVALUE('SlicerTable'[C_End])

RETURN
CALCULATE(
    SUM('BookDtl'[BookAmt]),
    FILTER(
        'BookDtl',
        'BookDtl'[Serial] >= SerialStart &&
        'BookDtl'[Serial] <= SerialEnd
    )
)

This measure constructs the SerialStart and SerialEnd values using the SELECTEDVALUE function, which retrieves the selected number from each slicer. The calculation follows the AAAABBCC format by multiplying the values accordingly. The FILTER function is then applied to 'BookDtl' to ensure that only serial numbers within the specified range contribute to the total sum of BookAmt. If multiple selections are allowed in the slicers, MIN and MAX can be used instead of SELECTEDVALUE to avoid blank values when multiple options are chosen. This approach ensures that the filtering logic works correctly across different slicer selections.

Best regards,

[trathman]

I am still getting nothing in the table visual. Can you expand on MIN and MAX? I've only been working with Power BI for about a year.

[trathman]

Thank you for the suggestion and I will try it and let you know what happens. I should refine what I had as  Variables SerialStart and SerialEnd refer to measures that were defined as:

SerialStart=

VAR ASerial = SELECTEDVALUE('SlicerTableA'[ValueA])
VAR BSerial = SELECTEDVALUE('SlicerTableB'[ValueB])
VAR CSerial = SELECTEDVALUE('SlicerTableC'[ValueC])

VAR Result = (ASerial*10000)+(BSerial*100)+CSerial

RETURN Result

Same structure for SerialEnd, using different slicer tables.