CODEWITHSUNDEEP
powerbissasmdx
Ethan1701
Ethan1701

Reputation: 193

MDX query for percent of current row within table

I have an SSAS cube with a FactTicket, that has a TicketsSold measure. The cube has a good number of dimensions. Lets call them Date, Agent and Geo. I can create a table (we're using Power BI service, if it makes a difference) and put in it all and any dimensions, and the TicketsSold measure. What I'd like to do, is have another measure that'll calculate the percent of each row in the table out of the total of the table at that moment. so suppose I have the following data:

Date        Agent       Geo     TicketsSold
2020-04-01  Moulder     US      12
2020-04-01  Moulder     UK      4
2020-04-02  Moulder     US      10
2020-04-03  Moulder     UK      5
2020-04-01  Skully      US      16
2020-04-01  Skully      UK      12

I would like to be able to filter my report on any measure, display any other measure(s) with the TicketsSold and the percent. Such as, filtered on 2020-04-01 and 2020-04-02:

Agent       Geo     TicketsSold     %ofTicketsSold
Moulder     US      22              40.74 %
Moulder     UK      4               7.40 %
Skully      US      16              29.63 %
Skully      UK      12              22.22 %
TOTAL               54              100 %

Or filtered only on Agent Moulder:

Date        TicketsSold     %ofTicketsSold
2020-04-01  16              51.61 %
2020-04-02  10              32.26 %
2020-04-03  5               16.13 %
TOTAL       31              100 %

It's clear to me that what I probably need is some way of getting -- for each row in the table -- the total of the table itself, and then dividing the row's own TicketsSold by this total. But I can't seem to figure out a trivial way of creating such a calculation. Is this doable, without having to define a tuple of all possible dimensions?

Thanks!

Upvotes: 0

Views: 460

Answers (2)

Lukas Hillesheim
Lukas Hillesheim

Reputation: 1

If you are already using PBI: why don't you make use of DAX? A DAX query is able to solve your problem: MEASURE Fact[Percent] = CALCULATE(SUM(Fact[Amount]), ALLSELECTED(Fact)). "ALLSELECTED" is similar to VISUALTOTALS() in MDX, but more dynamic.

Upvotes: 0

MoazRub
MoazRub

Reputation: 2911

You can achive that using the axis function . Below is an example based on Adventureworks where we will create a PercentageOFTotal Column based on InternetSales

select 
{[Measures].[Internet Sales Amount]}
on columns,
non empty  {([Product].[Category].[Category],[Product].[Subcategory].[Subcategory])}
on rows 
from 
[Adventure Works]
where [Date].[Calendar Year].&[2013]

Result enter image description here

Now lets add our new column

with member measures.PercentageOFTotal
as round(([Measures].[Internet Sales Amount]/sum(Axis(1),[Measures].[Internet Sales Amount]))*100,2)
select 
{[Measures].[Internet Sales Amount],measures.PercentageOFTotal }
on columns,
non empty  {([Product].[Category].[Category],[Product].[Subcategory].[Subcategory])}
on rows 
from 
[Adventure Works]
where [Date].[Calendar Year].&[2013]

Result

enter image description here

Upvotes: 2

Related Questions