Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

summing up multiple kpi's

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.  

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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
    )
    )

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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
        )
        )