Forum Discussion

lc1's avatar
lc1
Icon for Helper III rankHelper III
3 years ago
Solved

How to exclude date and url column from subtotal

Good afternoon,

I have a list of Funds, Dates, URL, Amount and Item number.

I need to subtotal only the Amount.  I do not want all other information in my subtotal row.

Please help,

Thanks 

LC

 


 Updating Media

 

Need Help
  • v-yinliw-msft's avatar
    v-yinliw-msft
    3 years ago

    Hi lc1 Qyntius,

     

    I think you can try this method:

    I think you can use SELECTEDVALUE function in the columns that you don’t want to show the total.

    And use the ALLEXCEPT function in the Amount.

    Here is my test.

     

    I new two measures of the BCC Date and First Items use the SELECTEDVALUE function.

    MeasureD = CALCULATE(MAX('Table'[BCC Date]), FILTER('Table', 'Table'[Fund Category] = SELECTEDVALUE('Table'[Fund Category])) )
    MeasureF = CALCULATE(AVERAGE('Table'[First Items]), FILTER('Table', 'Table'[Fund Category] = SELECTEDVALUE('Table'[Fund Category])))

    And I use the ALLEXCEPT function to create the Amount.

    MeasureA = CALCULATE(SUM('Table'[Amount]), ALLEXCEPT('Table','Table'[Fund Category]))

    Then create the matrix:

     

     

    In the TESTTABLE, I create an easy test.

     

    MeasureVALLEXCEPT = CALCULATE(SUM('TESTTABLE'[Value]), ALLEXCEPT('TESTTABLE','TESTTABLE'[ID]))
    MeasureVSELECTEDVALUE = CALCULATE(SUM('TESTTABLE'[Value]), FILTER('TESTTABLE', 'TESTTABLE'[ID] = SELECTEDVALUE('TESTTABLE'[ID])))

     

     

    Hope this helps you. Here is my PBIX file.

     

     

     

     

    Best Regards,

    Community Support Team _Yinliw

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

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    lc1 First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

     

    Try:

    BCC Date = 
  VAR __Count = COUNTROWS('Table')
  VAR __CountAll = COUNTROWS(ALLSELECTED('Table'))
  VAR __Return = IF(__Count = __CountAll, BLANK(), <whatever you are doing now>)


    This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

    Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
    https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

    Also: https://youtu.be/uXRriTN0cfY
    And: https://youtu.be/n4TYhF2ARe8

    • lc1's avatar
      lc1
      Icon for Helper III rankHelper III
      3 years ago

      Thank you Greg for your quick response, but I still have issues, this is new to me. See screenshot below:

       

  • v-yinliw-msft's avatar
    v-yinliw-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi lc1 Qyntius,

     

    I think you can try this method:

    I think you can use SELECTEDVALUE function in the columns that you don’t want to show the total.

    And use the ALLEXCEPT function in the Amount.

    Here is my test.

     

    I new two measures of the BCC Date and First Items use the SELECTEDVALUE function.

    MeasureD = CALCULATE(MAX('Table'[BCC Date]), FILTER('Table', 'Table'[Fund Category] = SELECTEDVALUE('Table'[Fund Category])) )
    MeasureF = CALCULATE(AVERAGE('Table'[First Items]), FILTER('Table', 'Table'[Fund Category] = SELECTEDVALUE('Table'[Fund Category])))

    And I use the ALLEXCEPT function to create the Amount.

    MeasureA = CALCULATE(SUM('Table'[Amount]), ALLEXCEPT('Table','Table'[Fund Category]))

    Then create the matrix:

     

     

    In the TESTTABLE, I create an easy test.

     

    MeasureVALLEXCEPT = CALCULATE(SUM('TESTTABLE'[Value]), ALLEXCEPT('TESTTABLE','TESTTABLE'[ID]))
    MeasureVSELECTEDVALUE = CALCULATE(SUM('TESTTABLE'[Value]), FILTER('TESTTABLE', 'TESTTABLE'[ID] = SELECTEDVALUE('TESTTABLE'[ID])))

     

     

    Hope this helps you. Here is my PBIX file.

     

     

     

     

    Best Regards,

    Community Support Team _Yinliw

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

  • lc1's avatar
    lc1
    Icon for Helper III rankHelper III
    3 years ago

    Thank you so much, it worked!