summing up multiple kpi's

[Anonymous]

Hello Community  -  

So our ERP system kicks out a bunch of snapshot data and KPIs  (we call them critical numbers).    Using the filter pane, I can select the fields and filter to get to a sum value, such as in the example below.   

However, what I really need is to be able to sum these KPIs  (critical number) to acheive the same thing using a Measure.  I've tried using Calculate and Filter to achieve this, but I can't make it happen.     And ideally what I need is the measure to be based on TODAY.    So that each day, it dynamically updates.    

Also, I currently have this Critical Number set to a "whole number".   It really should probably be a text field, but if I change it, I lose the ability to put it into a visual (gives me an error message).   

That's the least of my concerns as the main thing I really need is how to create a measure that sums up each of these items in the "description" field below, based on an "as of date"  of  today.  

[Greg_Deckler]

Difficult to be exact without source data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

But hopefully this is close enough to get you where you need to be. 

Measure = 
  VAR __Today = TODAY()
  VAR __Table = 
    GROUPBY(
      FILTER(
        'Table',
        [Critical Number] = 4292 &&
          [As Of Date] = __Today
      ),
      [Description],
      "__Actual Value",SUMX(CURRENTGROUP(),[Actual Value])
    )
RETURN
  SUMX(__Table,[__Actual Value])

[Anonymous]

Hi @Greg_Deckler 

Thanks for the quick reply.   I tried your formula but I think what it is missing is the descriptions of the respective codes  (that may not have been obvious from my initial post).    I ended up with this, which works well, other than it extends out past months where there is no data  (shows the same value for the remaining months in a bar chart for example).   Not a huge deal and I think I can figure that out.  

Not sure if there is a way to adapt your formula as I know there are always several ways to skin the cat in PBi.   

Posted YTD Revenue =

CALCULATE (

    SUM ( Flu_Snapshots[Actual Value] ),

    FILTER (

        Flu_Snapshots,

        ( Flu_Snapshots[Description])

            IN { "FLU All Revenue Accounts YTD - 40000", "FLU All Revenue Accounts YTD - 40005", "FLU All Revenue Accounts YTD - 40010", "FLU All Revenue Accounts YTD - 40075", "FLU All Revenue Accounts YTD - 40300", "FLU All Revenue Accounts YTD - 40400", "FLU All Revenue Accounts YTD - 49000" }),Flu_Snapshots[As Of Date]=TODAY())*-1

[Anonymous · Accepted solution]

@Greg_Decklerwith your help I was able to get to a solution that worked.   Thanks so much!

Here is what I used: 

Reconciled YTD Revenue2 = CALCULATE('Flu Shipped'[Total Shipped Value],FILTER('Flu Shipped','Flu Shipped'[Salesperson Group]<>"Osram"),Flu_Snapshots,

        ( Flu_Snapshots[Description])

            IN { "FLU All Revenue Accounts YTD - 40000", "FLU All Revenue Accounts YTD - 40005", "FLU All Revenue Accounts YTD - 40010", "FLU All Revenue Accounts YTD - 40075", "FLU All Revenue Accounts YTD - 40300", "FLU All Revenue Accounts YTD - 40400", "FLU All Revenue Accounts YTD - 49000" },

    CALCULATETABLE(

    DATESYTD ( Dates[Date] ),

    Dates[DatesWithShipments] = TRUE

)

)

[Greg_Deckler]

Well, you could always turn that into a VAR and then have a quick check in your RETURN to return BLANK perhaps.