Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Summing a measure

Hi,   I have a dataset on a list of assets which will need to be replaced as time goes on. Each has a different lifecycle so they will expire at different times. The table below is a sample and con...
Need Help
LivioLanzo's avatar
LivioLanzo
Icon for Solution Sage rankSolution Sage
7 years ago

Hi Anonymous

 

you need to create an iteration with SUMX over the main_asset_number, i can-t provide the exact measure as i dont have access to your data to test it.

 

but something which would make your measure (and your life) much easier, would be to modify your model to something like the below, and your measure would become a simple sum

 

for the first asset number:

 

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thanks LivioLanzo! I agree regarding the change in the model however unfortuantely we have far too many assets for this approach to be feasible.

 

I have tried to iterate over the SUMX as follows however am still getting some funky results

AllItems = 
SUMX(
VALUES(Assets_Medical[Main_asset_number]), [Measure])

I have uploaded a sample dataset and if you are able/willing to help further that would be greatly appreciated.

 

Many Thanks

Ben

  • v-cherch-msft's avatar
    v-cherch-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi Anonymous

     

    You may check below measure:

    Measure 2 = 
VAR myTable =
    CALCULATETABLE (
        ALL ( Assets_Medical[Main_asset_number] ),
        ALLEXCEPT ( DimDate, DimDate[FiscalYearText] )
    )
RETURN
    IF (
        HASONEFILTER ( Assets_Medical[Main_asset_number] ),
        [Measure],
        SUMX ( myTable, [Measure] )
    )

    Regards,

    Cherie

    • v-cherch-msft's avatar
      v-cherch-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      7 years ago

      Hi Anonymous

       

      Could you tell me if your problem has been solved? If it is,kindly mark the helpful answer as a solution and welcome to share your own solution. More people will benefit from here. If not, please share more details for us so that we could help further on it.

       

      Regards,

      Cherie