Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi, I have this problem:
I need to calculate an indicator for any product, the indicator should be calculated this way:
1) Sum of the cost.
2) That cost is divided by an area and the result it´s the indicator.
Notes: For any product the cost should be the same because the cost it's the sum of everything and the area it's the total area so it's the same for any calculation.
I have used in the DAX formula the function ALLEXCEPT to remove the filters of the product but when I change of product in the slicer, it changes the value so it seems to me that the ALLEXCEPT it´s not working at all. Here it´s what happens.
When I have selected All the products and category products, that should be the value:
But when I select a product the numbers change and that should not happen, the result must be the same.
The DAX formula I have it´s:
- Cto Admon = CALCULATE(sum(BaseDistribuida[Dolare2]),Dim_MapaCuentaII[Level03]="23 . Gastos Admon",Dim_Escenario[NombreEscenario]="Ejecución",ALLEXCEPT(Dim_FlorAnColor,Dim_FlorAnColor[GroupAnalysis02],Dim_FlorAnColor[FlorAnColor],Dim_FlorAnColor[SubGroupAnalysis02]))
-Cto Ventas = CALCULATE(sum(BaseDistribuida[Dolare2]),Dim_MapaCuentaII[Level03]="24 . Gts Ventas",Dim_Escenario[NombreEscenario]="Ejecución",ALLEXCEPT(Dim_FlorAnColor,Dim_FlorAnColor[GroupAnalysis02],Dim_FlorAnColor[FlorAnColor],Dim_FlorAnColor[SubGroupAnalysis02]))
Thanks.
Solved! Go to Solution.
@Anonymous,
ALLEXCEPT() function will not ignore the slicers you create, use ALL() function instead. Change your DAX to the following:
-Cto Admon = CALCULATE(sum(BaseDistribuida[Dolare2]),Dim_MapaCuentaII[Level03]="23 . Gastos Admon",Dim_Escenario[NombreEscenario]="Ejecución",ALL(Dim_FlorAnColor[GroupAnalysis02],Dim_FlorAnColor[FlorAnColor],Dim_FlorAnColor[SubGroupAnalysis02]))
-Cto Ventas = CALCULATE(sum(BaseDistribuida[Dolare2]),Dim_MapaCuentaII[Level03]="24 . Gts Ventas",Dim_Escenario[NombreEscenario]="Ejecución",ALL(Dim_FlorAnColor[GroupAnalysis02],Dim_FlorAnColor[FlorAnColor],Dim_FlorAnColor[SubGroupAnalysis02]))
Regards,
Lydia
@Anonymous,
ALLEXCEPT() function will not ignore the slicers you create, use ALL() function instead. Change your DAX to the following:
-Cto Admon = CALCULATE(sum(BaseDistribuida[Dolare2]),Dim_MapaCuentaII[Level03]="23 . Gastos Admon",Dim_Escenario[NombreEscenario]="Ejecución",ALL(Dim_FlorAnColor[GroupAnalysis02],Dim_FlorAnColor[FlorAnColor],Dim_FlorAnColor[SubGroupAnalysis02]))
-Cto Ventas = CALCULATE(sum(BaseDistribuida[Dolare2]),Dim_MapaCuentaII[Level03]="24 . Gts Ventas",Dim_Escenario[NombreEscenario]="Ejecución",ALL(Dim_FlorAnColor[GroupAnalysis02],Dim_FlorAnColor[FlorAnColor],Dim_FlorAnColor[SubGroupAnalysis02]))
Regards,
Lydia
I have a similar request. I above I suspect might apply, however I can follow the logic due to it being to contextually to @Anonymous problem.
My problems is as follows:
How do I ensure a total figure is not affected by any filters selected by the user. I know I could use “Edit interactions” and switch of the filter interaction for the visual; however I would like to achieve this via DAX.
Background context:
I would like to ‘Distinct Count’ all items on a column. Each row represents a classroom workshop. Workshops are planned in advance on a yearly base. Occasionally a workshop needs to be cancelled due to below minimum confirmed attendees. To report all workshops scheduled Vs. those cancelled and those live I want an figure unaffected by any filters which I can use as a Denominator.
My inputs would be:
Table name: [Workshops Plan]
Total workshops scheduled or as a short hand to make it easier to read [TOTWrkshop. Sch]
[TOTWrkshop. Sch] = District Count of workshop IDs within column [IDs].
I would also like a total of cancelled workshops, which would be:
Total Cancelled Workshops i.e. [TOTWrkshop. Cnl]
[TOTWrkshop. Cnl] = District Count of workshop IDs within column [IDs] AND with a status of “Cancelled” within column [Workshop Status]
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |