Forum Discussion

Thunders's avatar
Thunders
Frequent Visitor
2 years ago
Solved

BI Measure

It's so easy on exel...but now on BI I'm trying to create aggregated measure - see below in excel. Any help appreaciated!  Thank you  
  • MrBrownn's avatar
    MrBrownn
    2 years ago

    Good morning Thunders,
    We need more details for the Quota, is it always 2500 ? or it's change by month ?

    Why you choose r
    You can for exemple use something like that : 
    __Mesure =
    Var Total = CALCULATE(SUM(Quota), ALL(Dates) // it will calculate all total of the quota
    Var Aggre = CALCULATE(SUM(Sales), Dates[Month] >= EOMONTH(MAX('Dates'[Month]), -3) //Calculate the sum of every 3 last months

    RETURN DIVIDE(Aggre, Total)

     

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Thunders ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Add one index column in Power Query Editor

2. Create a measure as below to get the percentage

Aggregated Att% =
VAR _culpart =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Index] <= SELECTEDVALUE ( 'Table'[Index] )
        )
    )
VAR _allquota =
    CALCULATE ( SUM ( 'Table'[Quota] ), ALLSELECTED ( 'Table' ) )
RETURN
    DIVIDE ( _culpart, _allquota )

Best Regards