Count of invoices below threshold DAX
Hi, I m trying to create a measure to count number of invoices below threshold, threshold table is defined as below. my measure is working somehow but not giving total number of invoices, there is something wrong. kindly need assistance. Filelink Invoice Count Below threshold = VAR CurrentInvoiceAmount = [Inv. Amt SAR] VAR ThresholdValue = CALCULATE( MAX(Matrix_Invoice_Value[Value]), TREATAS( VALUES(Route_Master[Country]), Matrix_Invoice_Value[Country] ), TREATAS( VALUES(Customer_Master[Trade Category]), Matrix_Invoice_Value[Customer Type] ), TREATAS( VALUES(Route_Master[Route Type3]), Matrix_Invoice_Value[Route Type3] ), TREATAS( VALUES(Route_Cat[Attribute]), Matrix_Invoice_Value[Attribute] ) ) RETURN CALCULATE( [Invoice Count], FILTER( Daily_invioces, AND( NOT (ISBLANK(ThresholdValue)), CurrentInvoiceAmount < ThresholdValue ) ) ) threshold table Measure result: Data modelSales Amount at transaction level (aggregate at higher level) measure
Hello, I am trying to create a measure that displays the Sales Amount at transacttion (opportunity) level, regardless of the other dimensions I pull in my report. This can be a calculated column and it will work just fine, but I need to use a parameter with this calculation in the future so it HAS TO BE A MEASURE. When I do this calculation: Sales Amount Oppty Level = CALCULATE([Sales Amount],REMOVEFILTERS(Prod[ProdName]),VALUES(Oppty[OpptyName])) i get the accurate amount at opportunity level, but, if i add dimensions to the report, I get a cartesian product (all dimension values x all dimension values). ALLEXCEPT works only when I use the Product Table Oppty Name dimension, but I need to use Oppty Name from Product Table and the Partner dimension will make cartesian product with Opportunity for some reason. Cannot Use Oppty Name from Oppty table since then the measure will show the total sales amount for all Oppty for every row. Sales Amount Oppty Level 2 = CALCULATE([Sales Amount],ALLEXCEPT(Prod,Prod[OpptyName])) If i switch ALLEXCEPT to the below: Sales Amount Oppty Level 3 = CALCULATE([Sales Amount],ALLEXCEPT(Oppty,Oppty[OpptyName])) I will get the Sales Amount, not the Oppty total for all rows. Please help me to see the total Oppty Amount in a visual where I can have Oppty Name, Prod Name, Partner without cartesian product, like (red is bad, since partners 1 and 2 are not connected to oppty 1): Please help! Here is the model
