Sell Through DAX measure
Hello everyone! I need some help with DAX. Basically, I have to calculate SELL_THROUGH of the stores (CD_LOJA_SAP) and skus (CD_EAN) where [SELLOUT] AND [POSICAO_ESTOQUE] are not blank ou zero. Here is an example, using CD_LOJA_SAP: 1. How it is at the moment: 2. How I want it to be: As you can see, the measure works correctly for the rows in the table. However, the issue lies with the "Total" row. I need it to exclude stores 1 and 2. My model: DAX measures: SELL_THROUGH = VAR MaxData = [MAX_DT_ESTOQUE] VAR Estoque = [POSICAO_ESTOQUE] VAR Sellout = [SELLOUT] VAR Estoque_Sellout = [POSICAO_ESTOQUE_SELLOUT] VAR ST = SWITCH( TRUE(), ISBLANK(Sellout) || ISBLANK(Estoque), BLANK(), DIVIDE(Sellout, Estoque_Sellout) > 1, 1, DIVIDE(Sellout, Estoque_Sellout) ) RETURN ST MAX_DT_ESTOQUE = VAR MaxData = CALCULATE(MAX(fEstoque[DT_ESTOQUE]), ALL(fEstoque),fEstoque[CHAVE_COLECAO_COMERCIAL] = SELECTEDVALUE('Coleções'[DS_COLECAO])) RETURN MaxData POSICAO_ESTOQUE = VAR MaxData = [MAX_DT_ESTOQUE] VAR PosicaoEstoque = CALCULATE( SUM(fEstoque[QT_ESTOQUE]), fEstoque[DT_ESTOQUE] = MaxData ) RETURN PosicaoEstoque SELLOUT = VAR MaxData = [MAX_DT_ESTOQUE] RETURN CALCULATE( SUM(fSales[VOLUME]), fSales[DT_PEDIDO] <= MaxData ) POSICAO_ESTOQUE_SELLOUT = [SELLOUT] + [POSICAO_ESTOQUE] Could anyone please help me? Thank you so much!
Does filter (All('TableName')) remove duplicates?
I am new to Power BI and am learning the basics and am having a tough time figuring out the following: I have a Product Table with columns for product name, brand, and color. I have a matrix showing Brand and a measure "RedProductscount" The page has a slicer on color. The measure uses All ( ) command I understand why the count is always the total count of all Red products no matter what color is selected in the slicer when the code in the measure is : RedProductscount = countrows(filter(ALL('Product'),'Product'[Color]="Red")) It ignores the color selected in the slicer and counts how many red products are. But I do not understand why it shows a count of 1 if I change the measure to apply ALL to the color column like so: RedProductscount = countrows(filter(ALL('Product'[color]),'Product'[Color]="Red")) The only thing I could think of is that 1 represents the count of "Red"s in a list of unique colors. But is it really that? Questions: If it *is* the count of reds in the list of unique colors, what makes All to remove duplicates in the color column and turn it into a unique color list? Does it mean that Filter(All('TableName')) will ignore duplicates and do we have to be careful when we apply ALL() command on a table? If it is not the count of reds in the unique list of colors, then what is 1?
calculate % Product codes not sold in all territories?
Expected output Sample data: QUANTITYORDERED 30 34 41 45 49 36 29 48 22 PRICEPERUNIT 95.7 81.35 94.74 83.26 100 96.66 86.13 100 98.57 ORDERLINENUMBER 2 5 2 6 14 1 9 1 2 SALES 2871 2765.9 3884.34 3746.7 5205.27 3479.76 2497.77 5512.32 2168.54 ORDERDATE 12/31/2019 0:00 3/12/2020 0:00 5/6/2020 0:00 6/30/2020 0:00 8/15/2020 0:00 9/2/2020 0:00 9/16/2020 0:00 9/23/2020 0:00 10/6/2020 0:00 PRODUCTLINE Motorcycles Motorcycles Motorcycles Motorcycles Motorcycles Motorcycles Motorcycles Motorcycles Motorcycles MSRP 95 95 95 95 95 95 95 95 95 PRODUCTCODE S10_1678 S10_1678 S10_1678 S10_1678 S10_1678 S10_1678 S10_1678 S10_1678 S10_1678 COUNTRY USA France France USA USA USA France Norway USA TERRITORY NA EMEA EMEA NA NA NA EMEA EMEA NA
