Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

help with Dax measure

Hi all,

 

First of all thanks for your help and your time.

Follwing the attached scenario, we have a table with some categories(for this example only have two but in the real scenario i have lot of them), periods and the value.

 

 

The objective here is to create a measure with the result of the column SumTotal that is the result of the sum of all the values in the column value grouped by categoria and period. The issue here is that imagine that I apply a filter using a slicer of a column periodo filtering only the period 1 and 3 like this:

 

 

As I have applied a filter, the Sum Total have to change. Is there any way to perform this?

 

I'm really lost with this , i hope you can help me solving this .

 

thanks

 

Need Help

7 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , Try measure like

    Category Total


    calculate(Sum(Table[Value]), filter(allselected(Table), Table[Category] =max(Table[Category]) ) )

     

    Category Period total

     

    calculate(Sum(Table[Value]), filter(allselected(Table), Table[Category] =max(Table[Category]) && Table[period] =max(Table[period]) ) )

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi amitchandak ,

       

      Thanks you so much, is working as expected at 90% but i have a question.

      As you can see when is not filteres , Category Total is giving a total of 77 i don't understand why, is happening the same with Category Total Period and the % between te column Value / Category Total. 

       

      Here the measure I'm using:

       

      Category Total = calculate(Sum('Table'[valor]),FILTER(ALLSELECTED('Table'),'Table'[categoria] = MAX('Table'[categoria])))
       
      Category period total = calculate(Sum('Table'[valor]),FILTER(ALLSELECTED('Table'),'Table'[categoria] = MAX('Table'[categoria]) && 'Table'[periodo] = MAX('Table'[periodo])))
       
      %measure = DIVIDE(SUM('Table'[valor]),[Category Total])
       

      Maybe i have to add something else or this the way of the measures works? if is like this , why in this case is showing 77 , 12 and 340% as a totals?

       

      Thanks 

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi amitchandak ,

       

      Did you have time to check my last post?

       

      Thanks

  • Tahreem24's avatar
    Tahreem24
    Icon for Super User rankSuper User
    4 years ago

    Anonymous  Try this Measure DAX:

    Measure = CALCULATE(SUM('Table'[Value]),ALLEXCEPT('Table','Table'[Category]))
     
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  Anonymous ,

    Here are the steps you can follow：

    1. Create measure.

    SumTotal =
IF(
    HASONEVALUE('Table'[periodo]),
SUMX(FILTER(ALLSELECTED('Table'),'Table'[categoria]=MAX('Table'[categoria])),[valor]),BLANK())
    Valor_measure =
IF(
    HASONEVALUE('Table'[periodo]),
  MAX('Table'[valor]),
   SUMX(FILTER(ALLSELECTED('Table'),'Table'[categoria]=MAX('Table'[categoria])),[valor]))

    2. Result:

     

    Best Regards,

    Liu Yang

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Anonymous ,

       

      thanks for your answe but still happening the same. As you can see in the photo, if no selection is done, still showing 77.

      i don' understand why .should be 262 right?

       

      thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  Anonymous ,

     

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

     

    Best Regards,

    Liu Yang

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