Forum Discussion

Frequent Visitor

8 years ago

Solved

Additional filter into measure

Hello everybody, I have a table of values ID CounterNumber ReqDateT DataDateT SummAPlus 68511 139 2018-02-06 17:14:45.000 2018-02-05 00:00:00.000 11632,8 68512...

Need Help

v-piga-msft

8 years ago

Hi dkushner,

From the description of your replay, you should create a calculated table first in the formula below to get the max value of SummAPlus for the each of CounterNumber by DataDateT.

Table =
SUMMARIZE (
    PowerCountersPreviousDay,
    PowerCountersPreviousDay[DataDateT],
    PowerCountersPreviousDay[CounterNumber],
    "Sumsingle", MAX ( 'PowerCountersPreviousDay'[SummAPlus] )
)

Then, you could the measure :

Bussiness Unit =
VAR count1sum =
    CALCULATE (
        SUM ( 'Table'[Sumsingle] ),
        FILTER ( ALL ( 'Table'[CounterNumber] ), 'Table'[CounterNumber] = 1 )
    )
VAR count139sum =
    CALCULATE (
        SUM ( 'Table'[Sumsingle] ),
        FILTER ( ALL ( 'Table'[CounterNumber] ), 'Table'[CounterNumber] = 139 )
    )
VAR count149sum =
    CALCULATE (
        SUM ( 'Table'[Sumsingle] ),
        FILTER ( ALL ( 'Table'[CounterNumber] ), 'Table'[CounterNumber] = 149 )
    )
RETURN
    count139sum + count1sum
        - count149sum

This picture of the result is below:

For more details, you could refer to this pbix file.

Hope it can help you!

Best Regards,

Cherry

v-piga-msft

Resident Rockstar

8 years ago

Hi dkushner，

From the details you provide, if I understand your requirement correctly, you could use Data Hierarchy to get the Business Units of Year, Month or Day. You could create the measure :

Business Units = 
var count1sum =CALCULATE(SUM(PowerCountersPreviousDay[SummAPlus]),FILTER(ALL(PowerCountersPreviousDay[CounterNumber]),PowerCountersPreviousDay[CounterNumber]=1)) 
var count139sum = CALCULATE(SUM(PowerCountersPreviousDay[SummAPlus]),FILTER(ALL(PowerCountersPreviousDay[CounterNumber]),PowerCountersPreviousDay[CounterNumber]=139) ) 
var count149sum = CALCULATE(SUM(PowerCountersPreviousDay[SummAPlus]),FILTER(ALL(PowerCountersPreviousDay[CounterNumber]),PowerCountersPreviousDay[CounterNumber]=149)) 
RETURN 
count139sum+count1sum-count149sum

You could refer to this picture below: