Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Imported SSAS Multidimensional measures shows incorrect values

Hi All,

 

We are trying to import SSAS multidimensional cube/dimensions in "Import" Storagemode.

We could not refer the Imported SSAS measures (columns in context of power bi desktop) in a new measure or a new column.

 

One of our requirement is to create new measure as below:

Eg: Below are two table structure in power bi (data from two diff. measure group of SSAS) with related columsn from dimensions

MG1 --> RecoAmount

MG2 --> AdjustedAmount

Measure = iferror(RecoAmonunt/AdjustedAmount),-1,blank())

 

Above measure is workfing fine when power bi report  connect to SQLrelational db. when tried with workaround i.e. converting field with sum(recoamount) etc, we get incorrect values. 

 

We appreciate your valuable suggestions. 

 

Thanks.

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    Anonymous , if RecoAmount and AdjustedAmount are columns then this should work

     

    Divide(Sum(MG1[RecoAmount]),Sum(MG2[AdjustedAmount]))

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi amitchandak 


      Thanks for your instant response!! unfortunately suggested formula stilll gives wrong numbers.

       

      Actually AdjustedAmount is a measure in power bi, which has the below calculation: 

      Here CalcAmount field is imported from cube and it might give right numbers if SUM fucntion is not used, as it is adding more and more. Please correct my understanding and suggest the way to use calcamount as it is without further aggregation.
      AdjustedAmount= CALCULATE(SUM('V_sales'[CalcAmount]), filter( DIM_Date, DIM_Date[ActualDate] <= EOMONTH([Last Period],0)))

       

       

      Thanks.