Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Regarding Matrix Column Total Calculation based on date selection

Hi,

I have to create a DAX which calculates the total of a column based on date and then divide the row total of that with the total value.

eg. 

So, i want to create a measure which take amount total value based on date and calculate amount(%) by dividing individual row by total amount in the same way as shown in table.

 

Kindly suggest.

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Thank you Guys for the help..

    I also tried using this :

     

    Total = CALCULATE(Table[Amount],ALLEXCEPT(Table,Table[Date))

     

    and it worked !!

     

    Thanks for your help as well.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi

     

    first measure , sales by day by variety==>

    Total sales = sum(Fruit[Amount])
     
    second measure , Total sales for the day==> 
    Total sales day = calculate(sum(Fruit[Amount]),REMOVEFILTERS(Fruit[Variety]))
     
    Third measure, calculate %
    %sales = divide(Fruit[Total sales],Fruit[Total sales day],0)

     

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

    Hi Anonymous ,

     

    You could create a measure as below:-

    Measure =
VAR total =
    CALCULATE (
        SUM ( 'Table (2)'[Amount] ),
        ALL ( 'Table (2)'[Variety] ),
        'Table (2)'[Date] = MAX ( 'Table (2)'[Date] )
    )
RETURN
    DIVIDE ( MAX ( 'Table (2)'[Amount] ), total )

    Refer attached file.

     

    BR,

    Samarth

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you Guys for the help..

    I also tried using this :

     

    Total = CALCULATE(Table[Amount],ALLEXCEPT(Table,Table[Date))

     

    and it worked !!

     

    Thanks for your help as well.