Forum Discussion

sadwick's avatar
sadwick
Frequent Visitor
6 years ago
Solved

Measure: calculation of %

Hello everyone!
Could you please help me with measure calculation of %.


I have data like this:
date                 product         amount        what I wish to do (like in excel) - %%
2020/04/21      apples           30                 30/900

2020/04/21      eggs              15                 15/900

2020/04/21      flour               900               900/900

2020/04/21     cinnamon     20                 20/900

 

And I need to find percentage of all product to flour. 

Need Help
  • Icey's avatar
    Icey
    6 years ago

    Hi sadwick ,

     

    Try this:

    Measure =
VAR __Date =
    MAXX ( ALLSELECTED ( 'Table' ), 'Table'[date] )
VAR __Amount =
    CALCULATE (
        MAX ( 'Table'[Amount] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[Product] ), 'Table'[Date] = __Date )
    )
VAR __Flour =
    MAXX (
        FILTER ( ALLSELECTED ( 'Table' ), [product] = "flour" && [date] = __Date ),
        [amount]
    )
RETURN
    IF ( MAX ( 'Table'[Date] ) = __Date, DIVIDE ( __Amount, __Flour ) )

     

     

    Best Regards,

    Icey

     

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

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Perhaps something like:

     

    Measure = 
  VAR __Amount = MAX('Table'[amount])
  VAR __Flour = MAXX(FILTER('Table',[product] = "flour"),[amount])
RETURN
  DIVIDE(__Amount,__Flour)
    • sadwick's avatar
      sadwick
      Frequent Visitor
      6 years ago

      Greg_Deckler thanks!
      But if this table is cumulative. And I need a percentage of all product to flour where date =max date?

      date                 product         amount        what I wish to do (like in excel) - %%
      2020/04/21      apples           30                 

      2020/04/21      eggs              15                 

      2020/04/21      flour               900             

      2020/04/21     cinnamon       20               

      2020/04/22     apples           60                 60/400

      2020/04/22      eggs              9                  9/400

      2020/04/22      flour               400             900/400

      2020/04/22     cinnamon      60                 60/400

      • Icey's avatar
        Icey
        Community Support
        6 years ago

        Hi sadwick ,

         

        Try this:

        Measure =
VAR __Date =
    MAXX ( ALLSELECTED ( 'Table' ), 'Table'[date] )
VAR __Amount =
    CALCULATE (
        MAX ( 'Table'[Amount] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[Product] ), 'Table'[Date] = __Date )
    )
VAR __Flour =
    MAXX (
        FILTER ( ALLSELECTED ( 'Table' ), [product] = "flour" && [date] = __Date ),
        [amount]
    )
RETURN
    IF ( MAX ( 'Table'[Date] ) = __Date, DIVIDE ( __Amount, __Flour ) )

         

         

        Best Regards,

        Icey

         

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

  • camargos88's avatar
    camargos88
    Community Champion
    6 years ago

    Hi sadwick ,

     

    Try this measure:

     

    Measure =
    VAR _flour = CALCULATE(SUM('Table'[amount ]); FILTER(ALL('Table'); 'Table'[product] = "flour"))
    RETURN DIVIDE(SUM('Table'[amount ]); _flour)
     
    Ricardo