Forum Discussion

kamil947's avatar
kamil947
Frequent Visitor
5 years ago
Solved

Sum by Individual Selection and Sum by Group

Hello,   I'm trying to show a dash that is built on the food slicer. Once the food is selected, I'd like to display the amount of that specific food and the amount in the specific group. For exampl...
Need Help
  • TheoC's avatar
    TheoC
    5 years ago

    @kamil947, apologies. I misread the part that you wanted the "Sum of Group" in the other Card. 

     

    Although @Kevin_Harper has provided a very good output in terms of Calculated Columns, there is a challenge with using this for the output as evident below.  The total of the Calculated Column totals the amounts at the row level and therefore can overstate the actual output.  

     

    I recommend using a Measure as per below which provides the total amount of 26 when nothing is selected but also achieves the group subtotal in the Card visual itself.

     

     

     

     

    The Measure is as follows:

    Measure = 

    SUMX ( 
        VALUES ( tableFood[Group] ) , 
        CALCULATE ( SUM ( tableFood[Amount] ) , ALLEXCEPT ( 'tableFood' , tableFood[Group] ) )
        )

     

    All the best.

kamil947's avatar
kamil947
Frequent Visitor
5 years ago

This support has been amazing, thank you to everyone.

 

TheoC If I wanted to add a date aspect to this with a date slicer and dates next to the food, how would that alter the measure you provided?

  • TheoC's avatar
    TheoC
    Icon for Community Champion rankCommunity Champion
    5 years ago

    kamil947 I definitely recommend using a Date table. They can be easily generated using DAX and the "New Table" button on the Model ribbon.

     

    Here is a link to a simple DAX table:  https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

     

    From there, you will need to add additional a column of data into your fact table to enable relevance to occure between you Date table and Fact table.

     

    Otherwise, you can just add the Date data to you existing table: it just wouldn't really be "best practice" and you may find that some standard approaches using DAX and dates together may not work perfectly (I.e. you may require workarounds and all thT).