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 v...
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
    )
    )
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity 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
    )
    )
  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

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