Calculate with multiple criteria and different tables with measures
Hello everyone, I have a database that consists of 3 Tables: Item Material, Data, Sales_all_Years Item Material contains all items that are inside the database Data contains list price and target price for those items Sales_all_Years contains sales for those items in 2022 and 2023 I have a measure that was created inside the table Sales_all_Years, which intends to calculate change in sales between 2022 and 2023 in percent. I looks like this and work perfectly. % Change Sales = VAR cur = CALCULATE ( SUM ( 'Sales_all_Years'[Sales] ), FILTER ( 'Sales_all_Years', 'Sales_all_Years'[Year] = [Year max] ) ) VAR previous = CALCULATE ( SUM ( 'Sales_all_Years'[Sales] ), FILTER ( 'Sales_all_Years', 'Sales_all_Years'[Year] = [Year max] - 1 ) ) RETURN IF( AND( NOT(ISBLANK(cur)), NOT(ISBLANK(previous))), DIVIDE(cur - previous, previous), BLANK() ) I have another measure that calculates percent deviation from ListPice and Regional Target Price, which works and looks like this Percent deviation from Target Price Sales = if(isblank([ListPrice]),0, if([ListPrice] = [Regional Target Price], 0, if([ListPrice] > [Regional Target Price], ([ListPrice] - [Regional Target Price])/[ListPrice], if([ListPrice] < [Regional Target Price], (([ListPrice] - [Regional Target Price])/[ListPrice]))))) Now, I am trying to modify the first measure and create 2 additional measures that 1) calculate % Change Sales only for items with List Price < Regional Target Price (Percent deviation from Target Price Sales < 0) 2) calculate % Change Sales only for items with List Price > Regional Target Price (Percent deviation from Target Price Sales > 0) Any help is appreciated PBI dataset is added to the posted topic. https://www.dropbox.com/scl/fi/206bdkzdja7qetjqd1n4g/PBIX_example.pbix?rlkey=eu7u00uyc409lxk51eh8bptjm&dl=0 https://buschvacuum-my.sharepoint.com/:u:/g/personal/pavlo_shlapa_busch_de/EZwg4G827U9EslBfMk8_ZJsBHo5hZ5xuJ5vhgrvNQiWgqw?e=ohGgOG Sincerely, Pavlo
Count of Total Rows Using Filter Function Based on the Value of Multiple Measures
Plase see the screenshot below. Could someone please tell me how to correct the DAX to make the table visual result match the card visual result? Card visual result should show "1" for this scenario. I have also attached the PBI file. https://www.dropbox.com/s/x6pt0s36yc2oci5/Filter%20Function.pbix?dl=0 Basically, what is wrong witht the DAX below? Total CV Increase W/O EAC Increase where there is incurred cost = CALCULATE( COUNT(Project[Project #]), FILTER(Project, Project[Total Actual Cost] <> 0 && [Total CV Change] > 0 && [Total EAC Change] <= 0 ) )
Calculate number of sum of values if Entity exists in all the selected years
Hello Community, I am trying to create a measure that calculates sum of values based on a condition (i.e. only it the Entity exists in all of the selected years). For example: From the below table When the years "2022" and "2021" are selected the calculation/graph should display the sum of values only for "Meat and Fruit" entities (Veggies will not be included as there is no entry for Veggies in 2022). Below are the tables: Unit table: Entity Year unit Fruit 2022 100 Fruit 2021 200 Fruit 2020 500 Veggies 2021 100 Veggies 2020 40 Meat 2022 150 Meat 2021 250 Description Table: Entity Description Year Values Fruit Apple 2022 10 Fruit Pear 2022 20 Fruit Mango 2022 15 Fruit Orange 2022 25 Fruit Grape 2022 30 Fruit Papaya 2021 10 Fruit Apple 2021 15 Fruit Pear 2021 10 Fruit Mango 2021 15 Fruit Orange 2021 60 Fruit Grape 2021 40 Fruit Kiwi 2021 50 Veggies Carrot 2021 30 Veggies Beans 2021 20 Veggies Cucumber 2021 40 Veggies Cabbage 2021 10 Veggies Carrot 2020 10 Veggies Beans 2020 10 Veggies Cucumber 2020 10 Veggies Peas 2020 5 Veggies Corn 2020 5 Meat Chicken 2021 50 Meat Duck 2021 40 Meat Pork 2021 50 Meat Turkey 2021 60 Meat Lamb 2021 50 Meat Chicken 2022 40 Meat Goat 2022 20 Meat Pork 2022 30 Meat Turkey 2022 60 Expected output: I m not sure if there is anyway to achieve this but appereciate any response. Thanks, AnthonyJoseph
Perform Calculations on one row based on values in other rows
EDIT: Correcting 'sample' data - the Record ID for Record Types "Campaign" and "Campaign by Placement" are identical for each Campaign entity; I originally entered each Record ID as unique. I've searched around some, but unfortunately not quite sure how to even phrase it properly - so my apologies in advance if similar questions have already been asked and answered on the forums! As a little background, I am an intermediate Excel user, but a relative novice to PowerBi and DAX Issue/Question: We are receiving an excel file with marketing campaign settings data, and we need to use it to create a report that will allow a user to quickly determine the 'maximum' possible bid for a given keyword/campaign/placement. I'll reproduce the basic data structure below, along with our current attempt at a dax measure, and an example of what we are hoping to produce in a powerBi report. Data structure: Record ID Record Type Campaign ID Campaign Name Bid Keyword (string) Strategy Placement Modifier UniqueID 1 Campaign UniqueID 1 Ad Campaign Name Dyn Up All UniqueID 1 Campaign By Placement UniqueID 1 Ad Campaign Name TOS 35% UniqueID 1 Campaign By Placement UniqueID 1 Ad Campaign Name ROS UniqueID 1 Campaign By Placement UniqueID 1 Ad Campaign Name PDP 0% UniqueID 2 Ad Group UniqueID 1 Ad Campaign Name 1.25 UniqueID 3 Ad Product UniqueID 1 Ad Campaign Name UniqueID 4 Keyword UniqueID 1 Ad Campaign Name 4 keyword 1 UniqueID 5 Keyword UniqueID 1 Ad Campaign Name 3.87 keyword 2 UniqueID 6 Keyword UniqueID 1 Ad Campaign Name 2.87 keyword 3 With the above data, we want to calculate for every Record Type "Keyword" what our maximum possible bid is based on the values in the "Strategy" and "Modifier" columns. The calculation is essentially: IF "Strategy" = "Dynamic Up" THEN (Bid+(Bid * Modifier) * 2), ELSE (Bid+(Bid * Modifier) This needs to be performed for each value of the "Campaign By Placement" record for each "Campaign ID" - there will always be three (3) Campaign By Placement records for each unique "Campaign" record. We have already made something similar to this in Excel, but for a variety of reasons this approach is not tenable as a permanent solution. As a demonstration, this is what we are producing in Excel: (We have a roughly 1,000 campaigns to perform these calculations for, and the settings data will be updated weekly) For Campaign "Ad Campaign Name" / Campaign ID "UniqueID 1" Strategy TOS PDP ROS Dyn Up 35% 0% 0% Keyword Bid Max TOS Max PDP Max ROS keyword 1 $4.00 $10.80 $8.00 $8.00 keyword 2 $3.87 $10.45 $7.74 $7.74 keyword 3 $2.87 $7.75 $5.74 $5.74 We tried creating the below measure in Powerbi to allow us to pull out the needed data (one for each value we needed extracted) - however it fails because we can't actually associate it back to the Keyword records. FPTOS Mod = CALCULATE( SELECTEDVALUE( 'campaign_settings'[Modifier] ), 'campaign_settings'[Placement] = "TOS" ) Any guidance or resources that anyone can share with us will be very appreciated!!
Multiplicar valores entre fechas
buen dias para todos agradezco su ayuda con una consulta necesito replicar una tabla de excel en BI el cual multiplica un valor de mayo por un valor de junio, pero no encuentro la manera de hacerlo, agradezco su ayuda si conocen una forma de lograr el calculo, muchas gracias!! adjunto imagen de la tabla que esta en excel y el calculo
