Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.

Reply
equlibrum52
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 pricefinancing_comapany_id
110010
220011
350010
41000 

 

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            
1010%90%
1150%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 1011
product11090 
product2100 100
product350450 
product41000  

 

 

1 ACCEPTED SOLUTION
Gengar
Resolver I
Resolver I

Hi @equlibrum52 ,

 

You can create a new table for matrix visual:

Gengar_0-1670157908060.png

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_1-1670157968324.png

Gengar_2-1670157987834.png

 

Hope this could help you!

Gengar

 

 

 

View solution in original post

1 REPLY 1
Gengar
Resolver I
Resolver I

Hi @equlibrum52 ,

 

You can create a new table for matrix visual:

Gengar_0-1670157908060.png

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_1-1670157968324.png

Gengar_2-1670157987834.png

 

Hope this could help you!

Gengar

 

 

 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.