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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
felipeblvieira
New Member

Create a Column with filtered results from other columns. Text X Numbers

powerbi.jpg

Hello everyone !!

I´ve being in trouble with this.

 

The objective is to know how many ( in porcentage ) one "vendedor" sold of the product called in the " linha " column compared to the total sold by the Loja 1 in the " nome loja " column.

 

So i can filter the result by the column ( nome loja ), by the column ( linha ), and by the column ( vendedor ) and get the percentage of the column "vendedor" compared to the whole " linha " column and with the " nome loja " column.

 

With that i may now the percentage one "vendedor" sold of one specific product " linha " compared as the total of " linha " sold by the loja 1, column " nome loja ".

 

I´ve tryed so far to create a new column using the calculate formula. But it returns an error as the column "linha" is text and not numbers.

If i could filter the result of the sum of the "qtde de itens" and the "% qtde de itens" using the other text columns i will be able to do the rest easy.

 

Thanks in advance.

 

 

 

3 REPLIES 3
v-sihou-msft
Microsoft Employee
Microsoft Employee

@felipeblvieira

 

Please help confirm if understand your requirement correctly.

For example, Vendor_1 sold thirty Linha_1 products, Vendor_2 sold seventy Linha_1 products. Linha_1 product is not sold by other Vendors. Totally one thousand Linha products were sold by LOJA_1. So we can get below two different percentages.

  1. Linha_1 products sold by Vendor_1 / Linha_1 products sold by Vendor_1 and Vendor_2 = 30 / (30 + 70) = 30%
  2. Linha_1 products sold by Vendor_1 / All Linha products sold by LOJA_1 = 30 / 1000 = 3%

If my understanding is correct, please refer to following steps:

  1. Create a measure for vendor sales.
    Vendedor_Sales = 
    CALCULATE ( SUM ( PRODUTOS[Qtde de Itens] ) )
    
  2. Create two measures for Loja sales, one for all Linha products and another one for specific Linha product.
    Loja_Sales_Of_All_Linha = 
    CALCULATE (
        SUM ( PRODUTOS[Qtde de Itens] ),
        ALL ( PRODUTOS )
    )
    
    Loja_Sales_Of_Specific_Linha = 
    CALCULATE (
        SUM ( PRODUTOS[Qtde de Itens] ),
        ALLEXCEPT ( PRODUTOS, PRODUTOS[Linha] )
    )
    
  3. Create two measures for two different percentages mentioned at the beginning.
    %_VendedorSales_vs_LojaSales_All_Linha = 
    DIVIDE ( [Vendedor_Sales], [Loja_Sales_Of_All_Linha] )
    
    %_VendedorSales_vs_LojaSales_Specific_Linha = 
    DIVIDE ( [Vendedor_Sales], [Loja_Sales_Of_Specific_Linha] )
    
  4. Select specific Linha product in the slicer and check the results.
    1.jpg
felipeblvieira
New Member

Any news on this topic please?

 

@felipeblvieira

 

Create this measures

 

qItems-Linha = CALCULATE(sum(PRODUCTOS[QItems]))

%Qitems = DIVIDE([qItems-Linha];CALCULATE(sum(PRODUCTOS[QItems]);ALL(PRODUCTOS[Linha])))

 

I hope this help




Lima - Peru

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Kudoed Authors