Dynamic grand total problem depending on switch selection
Hello community, I'm unsuccessfully trying to show the dynamic Grand Total as the sum of the columns depending on whether or not I select the "Scenario" filter to show more measures. If I select one measure the total appears but if I select two or more selections it does not appear The reason is that the measures do not talk to each other using the "Scenario" selector which is detached from the model and is used within the measures via the SWITCH function. I insert the measure, for simplicity I remove some scenarios Sum_Scenario_Year = //example of recalled measures //ACT CY = CALCULATE(SUM('CAPEX FOR ENG'[SCENARIO_VALUE]),'CAPEX FOR ENG'[SCENARIO] = "ACT" , DATESYTD(TIME[Date])) // ACT - 1 = CALCULATE(SUM('CAPEX FOR ENG'[SCENARIO_VALUE]),'CAPEX FOR ENG'[SCENARIO] = "ACT" , DATEADD(DATESYTD(TIME[Date]), -1, YEAR)) // PCL CY = CALCULATE(SUM('CAPEX FOR ENG'[SCENARIO_VALUE]),'CAPEX FOR ENG'[SCENARIO] = "PCL" , DATESYTD(TIME[Date])) VAR Scenario_value =SWITCH(SELECTEDVALUE('MAIN BASELINE'[MAIN BASELINE]), "ACT CY" , [ACT CY], "ACT -1", [ACT -1], "PCL CY" , [PCL CY], ) RETURN Scenario_value I tried to insert a logic for the total but it takes the sum of all the measures without distinction from the selected ones //if (hasonevalue('MAIN BASELINE'[MAIN BASELINE]),Scenario_value , [ACT CY]+[ACT -1]+[ACT -2]+[ACT -3]+[FCT CY]+[BDG CY]+[PCL CY]+[EST CY]+[CMT CY]+[FTR + 1]+[FTR + 2]+[FTR + 3]+[FTR + 4]+[FTR + 5]) The model involved is this one I am not sure if this is possible, but any help regarding this issue is greatly appreciated! Thank you in advance! FEST
How to dynamically use different measures at the Grand Total vs. Filtered
Hello, I have two different measures for calculating Turnover because the logic changes if the data is filtered: "Turnover % (Division)" and "Turnover % (Total)" I'd like to have a third measure that dynamically switches between the two measures depending on if it's evaluating at the Grand Total. The below screenshot is showing a Matrix but I'd also be using this where it's controlled via a slicer (where no selection is made in the slicer = Grand Total). I've previously been able to switch between measures dynamically using IF(ISFILTERED(...) but that isn't helping here, likely because I need the Grand Total to work in the absence of any filter selection. My current (not working) measure is below: Turnover (Dynamic) % = //Department or Division IF( ISFILTERED('DeptStructure'[Department Code]) || ISFILTERED('DeptStructure'[Division Code])) ,[Turnover % (Division)] , //Hierarchy IF( ISFILTERED(DeptStructure[Division Code])&ISFILTERED(DeptStructure[Department Code]) , [Turnover % (Division)] , //Else [Turnover % (Total)] )) Any help is greatly appreciated! Thank you!
Two Grand Totals in Matrix Visual based on some conditions
Hi All, I am trying two create a simple matrix visual where I would like to show two grand totals at the end of the matrix. The first grand total (total) is the overall sum of all the rows and another would be the sum based on some conditions, In the below data first total is the overall total, and the second total is where the quantity is 100 FYI - This is just sample data I am looking to apply this logic in a big matrix where I have to calculate the total based on certain values (sum where value is 1 or 2). I have tried several ways but unable to achieve it. Anyone, please help me with this. Data and expected output: Person Salary A 100 B 200 C 400 D 100 E 100 Total 900 Total(100) 200 Your quick support will be highly appreciated. Regards UpharFilter out but grand total should not be affected.
I have an original table as below - but I want to filter out countries with participants less than 5. Hence when i apply filter I get the following matrix - However, I want total as 13 only. How do I make my "total" in the matrix universal (should not be affected by filter) but at the same time, I should be able to filter out specific countries ?
Getting Correct Grand Totals Following Measure Column
Hi there, I've had some valuable help here building the following DAX measure that will provide me with totals based on conditions to achieve this: DAX measure is: Measure = VAR _0 = LASTDATE ( 'Table'[Date] ) VAR _1 = CALCULATE ( LASTDATE( 'Table'[Date] ) , ALLEXCEPT ( 'Table' ,'Table'[Country] ,'Table'[Asset Reference] ) ) VAR _2 = IF ( _1 = _0 , SUM ('Table'[Amount] ) , BLANK() ) RETURN _2 However, as you can see in the total in the table above, it's not a reflection of what is in the column. How can I achieve this?DAX Distinct Count Grand Total Showing incorrect value
Why my Calculation didnt give correct Grand Total. Grand Total will be 33 but showing 44. Please help. SUMX( VALUES(data[Date]), CALCULATE(DISTINCTCOUNT(data[Wrap-up]),DATESINPERIOD(data[Date],MAX(data[Date]),-2,DAY)) ) Same problem happened even I remove Distinct Count. Grand Total will be 38 but showing 48. SUMX(VALUES(data[Date]), CALCULATE(COUNT(data[Wrap-up]), DATESINPERIOD(data[Date],MAX(data[Date]),-2,DAY)) ) How do I get the correct Grand Total.Incorrect Total
Hi, I am dealing with the common problem of not having my totals add up correctly and I am unsure how to amend it. Please see the measure below where I am trying to find the number of outstanding days. Outstanding Days = IFERROR( (COUNT(DIM_Calendar[Days in Month])/('Measures Table'[Actual_Sales]/'Measures Table'[Average AR])), BLANK() )+0 Would appreciate if any help is given! 🙂 Thank you.
Summing total columns
Hi all, I've a problem regardring PowerBI, which I've tried to illustrate in excel. I've a matrix with Department in rows and Priority in columns and Low, Medium, High and SUM in values. My problem is that I only want to show the yellow column and not the three red columns. Could anybody please help? Thanks
