Forum Discussion

dkushner's avatar
dkushner
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's avatar
    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's avatar
v-piga-msft
Icon for Resident Rockstar rankResident Rockstar
8 years ago

Hi dkushner，

 

If I understand your requirement correctly, you could create group with New Group to  summarize consumption by several CounterNumbers. As the picture below:

 

 

For how to create the group, you could refer to this article.

 

For more details about the result picture above, you could refer to my pbix file here.

 

Additionally, you could provide your expectant result demo that I could help you create the visual.

 

In  your convenience, could you share a dummy pbix file which can reproduce the scenario, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

 

Best Regards,

Cherry