Sum a row using visible columns
I have created a table that allows a user to choose what data columns they wish to see (via field parameters). There are up to 4 possible values they can select from. I need to get a dynamic total across each row based on those selected columns. So, for example, if they choose all 4 severity levels, the first row would total 10, second row would be 3, etc. But if they chose only Sev2 and Sev4 (meaning only those 2 data columns would be visible), they would get 4 in the first, 2 in the second etc. There are other values that can be calculated as well (such as year over year percent of change and so on) but those would obviously be calculations similiar to this basic one. What formula would acheive that result?
Selected Value Blank
Hi Guys, So I have two tables : a. Name Master table b.Data table And a visual which contains all the names from the Name Master table Name in the "Data table" can be in 6 different columns. The Name Master table is related to all 6 Columns So my aim is to calculate an expression but for that I need to check in which of the 6 columns that name is present and then filter that "Data table" according in which column it has been found. Below is the Dax which I tried to write for returning the selected name but it is giving me blank at some places. Selected Name= SELECTEDVALUE( Name_Master_List[Name]) Please help me guys I cant think anymore.Calcular a capitalização de um período inicial até o final.
Senhores, estou com um problema em um relatório, com o seguinte cenário. Preciso exibir os custos calculando com a capitalização, que é a o IPCA do ano inicial, até o ano final. Tenho o range de 2013 até 2024, quando um dos anos iniciais ou finais está selecionado, o filtro funciona perfeitamente (2013 até 2020, 2013 até 2024, 2013 até 2016... 2017 até 2024, 2021 até 2024, 2020 até 2024 – etc.) O problema ocorre quando estou selecionando intervalos, por exemplo: 2014 até 2022, o filtro faz o seguinte cálculo: 2024-2022, e exibe como ano inicial o ano de 2013 (pois não tem 2012). 2017 até 2023, o ano inicial se torna o ano de 2016; Sempre seguindo esta lógica. Como posso contornar essa situação? DAX: Capitalização Tabela = // Capitalização utilizada para inverter os valores na tabela pelo custeio VAR ANO_INICIAL = SELECTEDVALUE('ANO INICIAL'[ANO]) VAR ANO_FINAL = SELECTEDVALUE('ANO FINAL'[ANO]) VAR CONTADOR = MAX(IPCA[ANO])-ANO_INICIAL VAR CONTADOR2 = (ANO_FINAL-CONTADOR) RETURN SWITCH(ANO_INICIAL; 2013; CALCULATE( PRODUCT(IPCA[2013]);IPCA[ANO]=CONTADOR2); 2014; CALCULATE( PRODUCT(IPCA[2014]);IPCA[ANO]=CONTADOR2); 2015; CALCULATE( PRODUCT(IPCA[2015]);IPCA[ANO]=CONTADOR2); 2016; CALCULATE( PRODUCT(IPCA[2016]);IPCA[ANO]=CONTADOR2); 2017; CALCULATE( PRODUCT(IPCA[2017]);IPCA[ANO]=CONTADOR2); 2018; CALCULATE( PRODUCT(IPCA[2018]);IPCA[ANO]=CONTADOR2); 2019; CALCULATE( PRODUCT(IPCA[2019]);IPCA[ANO]=CONTADOR2); 2020; CALCULATE( PRODUCT(IPCA[2020]);IPCA[ANO]=CONTADOR2); 2021; CALCULATE( PRODUCT(IPCA[2021]);IPCA[ANO]=CONTADOR2); 2022; CALCULATE( PRODUCT(IPCA[2022]);IPCA[ANO]=CONTADOR2); 2023; CALCULATE( PRODUCT(IPCA[2023]);IPCA[ANO]=CONTADOR2); 2024; CALCULATE( PRODUCT(IPCA[2024]);IPCA[ANO]=CONTADOR2) )DAX - Selected slicer/value and row in data column are same, then give a certain output
Hi all, I'm very new to Dax and I'm building a model in PowerPivot (which I will use in PowerBi when finished). I have a financial report where I'm converting multiple currencies to one reporting currency which is selected by a slicer. It is set up as in the following link: https://www.kasperonbi.com/currency-conversion-in-dax-for-power-bi-and-ssas/ I have an Exchange Rate table set up. However for each day I am missing the currency rate where the "From currency" is the same as the "to currency" => So where my Exchange rate needs to be 1. I thought of adding lines into my table but this would mean adding thousands of lines for multiple currencies on multiple dates. I thought of creating a dax formula where I can say => If the row in the facts table (can be any currency) is the same the reported (selected with slicer) currency, then I want the amount in the row to be multiplied by 1. Otherwise I want the row to be multiplied by the actual exchange rate. (see a pivot example below where the issue occurs when EUR to EUR is reported) My current measures are like this : TrnExchangeRate=MIN('Dim Exchange rates'[Exch. Rate]) This will get the lowest factor value from the fact table. Now to calculate the sales per transaction I will use SUMX to achieve this: TotalSales = =IF(HASONEVALUE(ReportCurrency[ReportCurrency]);SUMX('Fact PL Data';'Fact PL Data'[Amount in local currency]*[TrnExchangeRate])) However if my currency in PL DATA is "EUR" for example, and my reporting currency is also "EUR", then I currently get a blank value. Because I never have 1 as an exchange rate in my table. What would be the easiest dax formula to solve this? Or would you recommend me adding all these in the exchange rate table for each day?
