Measure: calculation of %

[sadwick]

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. 

[camargos88]

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

[Greg_Deckler]

Perhaps something like:

Measure = 
  VAR __Amount = MAX('Table'[amount])
  VAR __Flour = MAXX(FILTER('Table',[product] = "flour"),[amount])
RETURN
  DIVIDE(__Amount,__Flour)

[sadwick]

@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 · Accepted solution]

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.

[Greg_Deckler]

OK, so based on this Quick Measure: https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/td-p/985814

That would be something like:

Measure = 
  VAR __Date = MAX('Table'[date])
  VAR __Product = MAX('Table'[product]) //assumes product is in the visualization
  VAR __Amount = MAXX(FILTER('Table',[product] = __Product && [date] = __Date),[amount])
  VAR __Flour = MAXX(FILTER('Table',[product] = "flour" && [date] = __Date),[amount])
RETURN
  DIVIDE(__Amount,__Flour)