Sum of a measure in the line above in a matrix

[Quenjo]

Hi community,

I have a Matrix table with 4 lines which is quite simple :

The first line represents the company.

The second line represents the application.

The third line represents the servers.

The fourth line represents the database.

As you can see here, the column "Server Price" is a measure which, based on some other fields contained in the "Servers" table, will do some caculations to find out the price per server.

How is it possible, without creating another column, to have the total price of all the servers juste here : 

The price per server is given by the following formula : 

Server Price = (203.91 + price 1 + price 2 + price 3) * factor 1 * factor 2

Thank you for your attention !

Best regards,

Jonathan

[v-xiaotang]

Hi @Quenjo 

I just want to confirm if you resolved this issue? If yes, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

If you need more help, please let me know.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[SolomonovAnton]

hello 

I think you should use The ISINSCOPE function

server price total =
var _total = 	(203.91 + [price 1] + [price 2] + [price 3]) * [factor 1] * [factor 2]
var _general = [server price]
var _result = if(or(isinscope('Table'[application]),isinscope('Table'[company])),_total,_general)
return _result

[Quenjo]

Hey @SolomonovAnton ,

Thank you for the solution provided. However, let me reformulate the question :

If I display only two rows, I have the total price displayed under the "Application" line.

Which is given by the formula :

Server Price = 
SWITCH (
    TRUE (),
    ISINSCOPE ( databases[name] ), BLANK(),
    ISINSCOPE ( servers[name] ), SUMX(servers, [Prix par Serveur]),
    ISINSCOPE ( applications[name] ), SUMX(servers, [Prix par Serveur]),
    ISINSCOPE ( company[name] ), BLANK (),
    BLANK ()
)

However, when expanding the Matrix, I want to have this total right here :

So that, when publishing the report, I have an easy view on the price per server and the sum of these totals in a same view without having to "zoom in and out" in the hierarchies.

Hope it was a better explanation 🙂

Best regards,

Jonathan

[SolomonovAnton · Accepted solution]

---

@Quenjo wrote:

However, when expanding the Matrix, I want to have this total right here :

you should switch on toggles as you see at screen below 

[Quenjo]

@SolomonovAnton

Perfect answer ! This is what I was looking for 🙂

Now it adds the Total like I wanted. However, why is the "Total" not displayed under the "Total" line ? The "Prix BDD" and "Prix Serveurs" are a measure containing a SWITCH function.

Best regards,

Jonathan

[SolomonovAnton]

BLANK in grand total because you wrote it in SWITCH  measure 

Server Price =
SWITCH (
TRUE (),
ISINSCOPE ( databases[name] ), BLANK(),
ISINSCOPE ( servers[name] ), SUMX(servers, [Prix par Serveur]),
ISINSCOPE ( applications[name] ), SUMX(servers, [Prix par Serveur]),
ISINSCOPE ( company[name] ), BLANK (),
BLANK ()
)

[Quenjo]

@SolomonovAnton 

Well... Actually not :

Prix Serveurs =

SWITCH (

    TRUE (),

    ISINSCOPE ( databases[name] ), BLANK(),

    ISINSCOPE ( servers[name] ), SUMX(servers, [Prix par Serveur]),

    ISINSCOPE ( applications[name] ), SUMX(servers, [Prix par Serveur]),

    ISINSCOPE ( company[name] ), SUMX(servers, [Prix par Serveur])

)

[SolomonovAnton]

you should write in measure that you want to get as grand total 
for example:

Server Price =
SWITCH (
TRUE (),
ISINSCOPE ( databases[name] ), BLANK(),
ISINSCOPE ( servers[name] ), SUMX(servers, [Prix par Serveur]),
ISINSCOPE ( applications[name] ), SUMX(servers, [Prix par Serveur]),
ISINSCOPE ( company[name] ), BLANK (),
SUM(servers[Prix par Serveur])
)

[Quenjo]

@SolomonovAnton 

You are really helping me a lot through this implementation. Thank you ! 🙂

Just a last question I have about this :

The value in red is calculated as follows :

Prix de l'application = 

var _totalApp = (applications[Prix AM] * applications[Application Complexity Coeff]) + [Prix BDD] + [Prix Serveurs]

return 

SWITCH (
    TRUE (),
    ISINSCOPE ( databases[name] ), BLANK(),
    ISINSCOPE ( servers[name] ), BLANK(),
    ISINSCOPE ( applications[name] ), _totalApp,
    ISINSCOPE ( company[name] ), BLANK (),
    BLANK ()
)

I want to have the exact same value (2'374,66) in the correspoding line of the company and of the grand total. Please, note that if I just put "_totalApp" in the SWITCH function under the isinscope ( company[name]) and the grand total it will not work because it will not find the "[Prix AM] and the [Application Comexity Coeff] values.

Is there a way to just get the value of the cell in red and put it in the corresponding company line and in the grand total ?

Many thanks !

Best regards,

Jonathan

[SolomonovAnton]

hi Jonathan

yout matrix could be include several applications[name], so in upper group level (I mean company[name]) you should use some aggregation function like SUM, COUNT etc

if you want use in COMPANY level only data from some one APPLICATION try to do it

Prix de l'application = 

var _totalApp = (applications[Prix AM] * applications[Application Complexity Coeff]) + [Prix BDD] + [Prix Serveurs]

return 

SWITCH (
    TRUE (),
    ISINSCOPE ( databases[name] ), BLANK(),
    ISINSCOPE ( servers[name] ), BLANK(),
    ISINSCOPE ( applications[name] ), _totalApp,
    ISINSCOPE ( company[name] ), CALCULATION(_totalApp, FILTER(ALL(applications[name]), applications[name]=" here write for what exact value from applications[name] you want calculate ")),
    BLANK ()
)

if I helped you please click to give Kudos me )

[Quenjo]

@SolomonovAnton 

Unfortunately, this does not work. 

I want, under the grand total and the company line, to have the sum of all the application prices. However, with your code :

The values will not be the same because, I think, that in the formula of the _totalApp variable it does not find the [Prix AM] and the [Application Complexity Coeff]. 

As we can see : 2'374.66 - 1.5  [Application Complexity Coeff] * 113 [Prix AM] = 2'205.16

I want to take the SUM of all the applications under a company. But the EXACT Value contained in the corresponding cell. (2'374.66 in this case)

Please, de not hesitate if you have any questions 🙂

Best regards,

Jonathan

[SolomonovAnton]

could you show how you calculate [Application Complexity Coeff] and  [Prix AM] ?

[Quenjo]

@SolomonovAnton ,

Sure, [Prix AM] :

Prix AM = 
SWITCH (
    TRUE (),
    ISINSCOPE ( databases[name] ), BLANK (),
    ISINSCOPE ( servers[name] ), BLANK (),
    ISINSCOPE ( applications[Application] ), IF(applications[Application Management] = "Yes", 113.00, 0),
    ISINSCOPE ( company[name] ), BLANK (),
    BLANK ()
)

[Application Complexity Coeff] :

Application Complexity Coeff = 

Var _Coeff =

SWITCH(SELECTEDVALUE(applications[u_application_complexity]),
"High", 4,
"Mid", 1.5,
"Low", 1)

Var _Visual =
SWITCH (
    TRUE (),
    ISINSCOPE ( databases[name] ), BLANK (),
    ISINSCOPE ( servers[name] ), BLANK (),
    ISINSCOPE ( applications[Application] ), _Coeff,
    ISINSCOPE ( company[name] ), BLANK (),
    BLANK ()
)

Return
_Visual

Please, note that the measure [Prix de l'application] is on the "servers" table. Both the [Prix AM] and the [Application Complexity Coeff] are from the "applications" table.

Regards,

Jonathan

[SolomonovAnton]

hello Jonathan

you have same case there

Application Complexity Coeff =

Var _Coeff =

SWITCH(SELECTEDVALUE(applications[u_application_complexity]),
"High", 4,
"Mid", 1.5,
"Low", 1)

Var _Visual =
SWITCH (
TRUE (),
ISINSCOPE ( databases[name] ), BLANK (),
ISINSCOPE ( servers[name] ), BLANK (),
ISINSCOPE ( applications[Application] ), _Coeff,
ISINSCOPE ( company[name] ), BLANK (),
BLANK ()
)

Return
_Visual

Prix AM =
SWITCH (
TRUE (),
ISINSCOPE ( databases[name] ), BLANK (),
ISINSCOPE ( servers[name] ), BLANK (),
ISINSCOPE ( applications[Application] ), IF(applications[Application Management] = "Yes", 113.00, 0),
ISINSCOPE ( company[name] ), BLANK (),
BLANK ()
)

you should change it order you logic 

[Quenjo]

@SolomonovAnton 

The difference here is that I do not want to have the [Prix AM] and the [Application Complexity Coeff] to be displayed in the company line. This would not make any sense.

--> Any application can have a different [Application Complexity Coeff], so displaying the coefficient under the company line does not make any real sense...

I just want to SUM all the exact values of the [Prix de l'application] under the grand total and the company line, isn't there an easy way to implement this ? Seems pretty basic...