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

     

     

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
1 year ago

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.