Help with MDX calculations

[andrmh]

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 · Accepted solution]

Hi @andrmh,

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

[GilbertQ]

Hi @andrmh what type of MDX queries are you referring to?

MDX from OLAP Cubes, or rather DAX calculations inside Power BI?

[andrmh]

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]

Hi @andrmh 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