Forum Discussion

Anonymous

Not applicable

9 years ago

Solved

Help with MDX calculations

Hi All,
Im looking for some help with a rather simple query.

How do i get the sum of all quote values (call it value, and the measure quote), where the value is in a range (say 0 to 5000).

Also need a count of rows for the same range.

Thanks,

Andrew

Anonymous
9 years ago
Hi Anonymous,

For your requirement, you can add a calculate column to calculate the range. then use a measure to get the total value of each range.

Sample:
Calculate column.

Range = if([ValueColumn]>=0&&[ValueColumn]<=5000,"0~5000",if([ValueColumn]>5000&&[ValueColumn]<=50000,"5000~50000","other"))

Measures:

Total Value of Range=
var currentRange=LASTNONBLANK('Invoked Function'[Range],[Range])
Return
SUMX(FILTER(ALL(Table),[Range]=currentRange),[ValueColumn])

Count of Range=
var currentRange=LASTNONBLANK('Invoked Function'[Range],[Range])
Return
Count(FILTER(ALL(Table),[Range]=currentRange),[Range])

if above is not help, can you please share same sample data?

Regardss,
Xiaoxin Sheng

4 Replies

GilbertQ
Super User
9 years ago
Hi Anonymous what type of MDX queries are you referring to?

MDX from OLAP Cubes, or rather DAX calculations inside Power BI?
- Anonymous
  Not applicable
  9 years ago
  apologies.
  Its MDX.
  We are building our cubes in a Business Intelligence project (Via Visual studio), then consuming the resulting cube in PBI. Desktop
  
  if this is the wrong forum I apologise.
  - GilbertQ
    Super User
    9 years ago
    Hi Anonymous if you use the built in connector to your SSAS OLAP Cube, you should be able to just select the data that you want without writing MDX queries?
    
    This might be a handy reference
    
    https://jetsupport.jetreports.com/hc/en-us/articles/218952488-How-to-Connect-Power-BI-to-OLAP-Cubes
Anonymous
Not applicable
9 years ago
Hi Anonymous,

For your requirement, you can add a calculate column to calculate the range. then use a measure to get the total value of each range.

Sample:
Calculate column.

Range = if([ValueColumn]>=0&&[ValueColumn]<=5000,"0~5000",if([ValueColumn]>5000&&[ValueColumn]<=50000,"5000~50000","other"))

Measures:

Total Value of Range=
var currentRange=LASTNONBLANK('Invoked Function'[Range],[Range])
Return
SUMX(FILTER(ALL(Table),[Range]=currentRange),[ValueColumn])

Count of Range=
var currentRange=LASTNONBLANK('Invoked Function'[Range],[Range])
Return
Count(FILTER(ALL(Table),[Range]=currentRange),[Range])

if above is not help, can you please share same sample data?

Regardss,
Xiaoxin Sheng