cancel
Showing results for
Did you mean:
Frequent Visitor

A measure that can calculated values for categories other than the selected one

Hi

I have a problem to solve that I can't handle.

After simplification, I have two tables, the first one with the product. Where for each product we have the price and the financing company, the financing company may be blank (if we purchased the product with our own funds)

 productId price financing_comapany_id 1 100 10 2 200 11 3 500 10 4 1000

in the second table we have financing conditions for individual companies. That is, what percentage of the product amount is financed by own funds and what percentage by the financing company

 financing_company_id Downpayment Financing 10 10% 90% 11 50% 50%

What I would like to achieve is to create a measure that would calculate for each financing company the value of the capital involved in accordance with the terms of financing. That is, the price multiplied by the percentage of financing

That is, for product 1 it would be 100*90%=90

Complications occur with the financing company "blank" because for it, apart from the value of product prices for which financing_company is blank, I would also like to sum up the product values ​​multiplied by the downpayment value for products for which the financing company is not blank.

So for product 1, I would like for the category financing_company_id = blank to have the calculated price multiplied by downpayment so 100*10%= 10

This is what the table I want looks like

 product/financing_company_id 10 11 product1 10 90 product2 100 100 product3 50 450 product4 1000

1 ACCEPTED SOLUTION
Helper II

Hi @equlibrum52 ,

You can create a new table for matrix visual:

Then create a matrix visual and apply the measure:

``````Measure =
var Company = CALCULATE(MAX('Table'[financing_comapany_id]),FILTER('Table',[productId]=MAX('matrix'[productId])))
var Financing = CALCULATE(MAX('Table (2)'[Financing]),FILTER('Table (2)',[financing_company_id]=Company))
var DownPayment = CALCULATE(MAX('Table (2)'[Downpayment]),FILTER('Table (2)',[financing_company_id]=Company))
var Pricevalue = CALCULATE(MAX('Table'[price]),FILTER('Table',[productId]=MAX('matrix'[productId])))
var result1 = SWITCH(TRUE(),
MAX('matrix'[financing_comapany_id])=Company,Pricevalue*Financing,
ISBLANK(MAX('matrix'[financing_comapany_id])),DownPayment*Pricevalue,
BLANK())
return IF(ISBLANK(Company)&&ISBLANK(MAX('matrix'[financing_comapany_id])),Pricevalue,result1)``````

Gengar

Helper II

Hi @equlibrum52 ,

You can create a new table for matrix visual:

Then create a matrix visual and apply the measure:

``````Measure =
var Company = CALCULATE(MAX('Table'[financing_comapany_id]),FILTER('Table',[productId]=MAX('matrix'[productId])))
var Financing = CALCULATE(MAX('Table (2)'[Financing]),FILTER('Table (2)',[financing_company_id]=Company))
var DownPayment = CALCULATE(MAX('Table (2)'[Downpayment]),FILTER('Table (2)',[financing_company_id]=Company))
var Pricevalue = CALCULATE(MAX('Table'[price]),FILTER('Table',[productId]=MAX('matrix'[productId])))
var result1 = SWITCH(TRUE(),
MAX('matrix'[financing_comapany_id])=Company,Pricevalue*Financing,
ISBLANK(MAX('matrix'[financing_comapany_id])),DownPayment*Pricevalue,
BLANK())
return IF(ISBLANK(Company)&&ISBLANK(MAX('matrix'[financing_comapany_id])),Pricevalue,result1)``````

Gengar

Announcements

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors