Grouping based on single record item

DAX DAX Calculations
1

I have a scenario where I have a list of sales orders for my customers. Saleslorder will be flagged as billed or not, basically a true false flag. I’d like to sum the total of all sales orders for customers who have at least 1 salesofder flagged as false, so if at least 1 false all records, including true for customers number, is included.

I’m on mobile right now so I don’t have a sample handy. Any hints on how to tackle?

2

If I’m understanding you correctly, all you would need to do is create a new measure and filter out if billed is true or false. Do you just want the total amount of sales for false or true? If you have a column for SALES you could use a measure with SUMX, if not then you would need to use CALCULATE. So if you have a sales column, use SUMX and FILTER, and if not use CALCULATE & FILTER. Let me know what you come up with.

Thanks

Jarrett

Enterprise%20DNA%20Expert%20-%20Small

3

Not that simple unfortunately. If a customer is has say 10 orders, and only 1 is false, I want to sum all 10, the 1 false and 9 true. If customer has no false records, ignore all

4

My current train of thought is using a table function to create a list of customer IDs with the appropriate flag and then use that account id as the filter for the calculation to grab all orders for each account with at least 1 order that has the flag. Attached is a sample of the data structure. I would want to sum the total for all line items associated to 100256 and 100260, regardless of flag, because each has at least 1 F flag.

I have a couple of test measures in the attached, which appear to give me the correct totals, but only shows the single order number with the flag when looking at a grid view. One summing all orders, the other just the “F” orders, but correct totals. EDNA_Help.pbix (38.8 KB)SampleData.xlsx (9.0 KB)

5

If I create a table for the account numbers I want (Table 2 in file) and link it to the sampledata table, I can get the correct result. Is it possible to do this without that table though

6

Randy,

Sounds like you may be able to use a SWITCH statement to help you with your logic. Here is a link to one of Sam’s videos regarding SWITCH.

Your logic in the SWITCH statement would be

IF [Billing]= "False", [Total Billed Measure],
IF [Billing]= "True", BLANK(),
[Total Billed Measure]

You could use this measure to get the total billed per customer. Try that, and I will have a look at the files you sent over.

Thanks
Jarrett

Enterprise%20DNA%20Expert%20-%20Small

7

Figured it out using Variables and a SWITCH Statement. Here are some photos of results I got, and measure is called Billable T/F.
image

image

Thanks
Jarrett

Enterprise%20DNA%20Expert%20-%20Small

8

Thanks Jarret, but thats not quite what I am after. You’re results show for all accounts, I want to show for only accounts with at least 1 False Billable flag. I did get it to work by creating a table of account numbers that met he criteria and then creating a relationship. From there I use a measure such as:

Unbilled = CALCULATE( [Total Sales],

FILTER( RELATEDTABLE( 'Table 2'),'Table 2'[Account Number] <> BLANK() ),

USERELATIONSHIP('Sample Data'[Account Number], 'Table 2'[Account Number] ) )

Which gives what I am after. The new table should be very small in my larger data set, but I am curious if it is possible to do this all within a measure as opposed to creating the new table.
Copy of SampleData.xlsx (9.0 KB)
EDNA_Help.pbix (43.9 KB)

9

I’m a bit confused by your response. Only the accounts that had an “F” are completely totaled, and if it only has “T” then I made it zero. You can change my measure from zero to blank if that is what your looking for. You don’t need to do all the extra work that you are doing.

Thanks
Jarrett

Enterprise%20DNA%20Expert%20-%20Small

10

Hi Jarrett,

Apologies, I think I am explaining it wrong here. At the end, I need to show a grid with Acct #, Order #, Sale Amount. As soon as I add Order # to the grid, the results go to the Order level and zero out "t"orders associated to an account with an “f” order. I’m not sure how to get around that context change with SWITCH TRUE. Left grid below shows what my end result needs to be, which I can do with the table method.

EDNA_Help
EDNA_Help.png1073×545 13.8 KB
Does that make sense?

Thanks!
-Randy

11

Hi @Randy

I think this might be what your looking for:

Unbilled =
SUMX (
VALUES ( 'Sample Data'[Account Number] ),
IF (
    NOT ISEMPTY (
        CALCULATETABLE (
            'Sample Data',
            ALLSELECTED ( 'Sample Data'[Order Number] ),
            'Sample Data'[Billable] = "F"
        )
    ),
    [Total Sales]
)
)
12

Lost sight of this one. This is exactly what I was after @Gravanita and actually just helped me with a similar scenario