Forum Discussion

trathman's avatar
trathman
Regular Visitor
1 year ago
Solved

Summing a Range Based on Serial Numbers

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 fol...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

     

    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

     

     

     

DataNinja777's avatar
DataNinja777
Super User
1 year ago

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's avatar
trathman
Regular Visitor
1 year ago

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.