Need help to show field once per invoice key (key) and still show the rest of the other detail lines

[Preston]

Hello!

I need some help. In my data module I need to grab a freight column and only display it ONCE per invoice key well still displaying the other detail rows for this invoice. I have a screen shots below and the attached report for some reference. The issue is that the freight amount is on the header record, so when I link it to the detail lines it shows it for each detail record when I only need to show it once. But I still need to show each detail record individually because I need to add a slicer for the Product Line.
Below are screen shots and also my PowerBI report.

Any help is appreciated!!!

SalesDollarsByProduct.pbix (1.7 MB)

[Greg]

Hi @Preston. I don’t see a column that can be used to differentiate header records from detail records. You should be able to modify your base tables before combining the [AR_InvoiceHistoryHeader] and [AR_InvoiceHistoryDetail] tables such that you can identify the record that should have the freight amount. Once this column is available, you can write a measure to only return the freight value for header records.

In my attempt at doing the update in your file, I was stopped by the need for credentials and access to your database, so can only make general comments. (I noted that your [Calendar Table] was not marked as a date table, and suggest you mark it as such.)

Good luck.
Greg

[Harsh]

Hello @Preston,

Thank You for posting your query onto the Forum.

Is the result that you’re trying to achieve? Below is the screenshot provided for the reference -

For this you can use the formula provided below for the reference -

Total Freight = 
SUMX(
    SUMMARIZE(
        AR_InvoiceHistoryHeader , 
        AR_Customer[CustomerNo] ,
        AR_InvoiceHistoryHeader[FreightAmt] ) , 
    AR_InvoiceHistoryHeader[FreightAmt]
)

I’m also attaching the working of the PBIX file for the reference.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

SalesDollarsByProduct - Harsh.pbix (1.7 MB)

[Heather]

Or, if you do not want to create a new measure, will it work to switch your visual to a Matrix table?

You just need to change the Row Headers formatting to turn off the stepped layout:
and expand the columns out in the table options:

see attachedSalesDollarsByProduct - Heather.pbix (1.7 MB)

[Preston]

This is close…I think I left out an important part. I need to add together the “Freight” to the “Dollars” field as well. So in this example I need $0+$12+$360+$228 = $600. This would be the total for the 0047236000000 invoice, then I will need to total this up and display it in a card so that I can see totals including multiple invoices and customers.

Also, is there away to configure my file to save the data from the ODBC connection so you can get into PowerQuery? I thought I have shared reports with you guys and didn’t have this problem previously.

[Preston]

Is there away I can save the file so you don’t need the ODBC credentials? I have sent you files before that didnt have this issue.

[Greg]

Hi @Preston. Your report pulls data from a database, so unless that database is publicly accessible (which I doubt), then I don’t think so. Perhaps in the earlier PBIX files you sent before used other data sources.
Greg

[Preston]

That makes sense however all of the reports I have created are using an ODBC connection. O well, I will wait to hear back from @Harsh on my question. Thanks!

[Harsh]

Hello @Preston,

Below is the formula provided to achieve the result i.e. “Freight + Dollars = Total Freight” -

Total Freight - Consolidated = 
VAR Freight = 
SUMX(
    SUMMARIZE(
        AR_InvoiceHistoryHeader , 
        AR_Customer[CustomerNo] , 
        AR_InvoiceHistoryHeader[FreightAmt] ) ,
    AR_InvoiceHistoryHeader[FreightAmt]
)

VAR Dollars = 
IF( ISBLANK( SUM( AR_InvoiceHistoryHeader[FreightAmt] ) ) ,
    BLANK() , 
    CALCULATE( SUM( AR_InvoiceHistoryHeader[Dollars] ) ,
        ALLEXCEPT(
            AR_InvoiceHistoryHeader , 
            AR_InvoiceHistoryHeader[Key] 
        )
    )
)        

RETURN
Freight + Dollars

Now, once you create this measure you’ll get the correct results for individual line items but the “Grand Total” will be off by a long way. Below is the screenshot of the result provided for the reference -

So if you see individual line items are correct but grand total is not correct. Ideally, it should’ve been $20,703.29 i.e. Dollars + Freight (20,475 + 228).

So now, to achieve the correct grand total write a small formula. Below is the formula provided for the reference -

Total Freight - Cons. Total = 
SUMX(
    SUMMARIZE(
        AR_InvoiceHistoryHeader , 
        AR_Customer[CustomerNo] , 
        AR_InvoiceHistoryHeader[Key] , 
        "@Totals" , 
        [Total Freight - Consolidated] ) , 
    [@Totals] )

Since you wanted to show the results in the form of “Card Visual” that’s why we had to write the formula since card visual doesn’t have any context by default. @BrianJ had recently created a video based on this topic which addresses this issue in depth I’m providing a link below of that video. Otherwise there was a trick to fix the incorrect total without writing the formula. Just go to the FILTER PANE and under the “Dollars” field select the option of “is not blank” and you’ll see the totals getting fixed but this trick is not applicable for card visuals. Below is the screenshot provided for the reference -

I’m also attaching the working of the PBIX file for the reference.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

SalesDollarsByProduct - Harsh v2.pbix (1.7 MB)

Fixing Card Visual Results In Power BI

[Preston]

This is so close! I think I have been explaining this incorrectly, I apologize Let me explain what I need again.
See my attached screen shot for another explanation.

[Harsh]

Hello @Preston,

“This is close…I think I left out an important part. I need to add together the “Freight” to the “Dollars” field as well. So in this example I need $0+$12+$360+$228 = $600. This would be the total for the 0047236000000 invoice, then I will need to total this up and display it in a card so that I can see totals including multiple invoices and customers.” ---- This is what you’d requested in your previous post and accordingly the measures were written.

Now, you’ve mentioned that, "A ProductLine which is of $265 but the measure is showing the result as $750.68" ---- This is because earlier you suggested you want consolidation of “Freight + Dollars” amount and therefore it’s $221 from Freight + ($265 + $229 + $36) from Dollars and therefore it’s $750.68. Just like the concept that you’d mentioned in the previous post.

Now, you’re suggesting this is not again what you’re looking for. So now, here’s the thing, please provide the current status on the report and mock-up of the results that you’re trying to achieve alongwith clear and concise explanation of what’s actually required here because I’ve already invested quite a amount of time onto this query and the requirements are changing frequently. The last part of the paragraph that you’ve mentioned is quite confusing to me.

Thanks and Warm Regards,
Harsh

[Preston]

@Harsh I know I apologize the stakeholders on my side have changed things slightly. Lets do this. You provided me what I initially asked for so lets mark this case as complete and I will open a new one just to keep things clear. I apologize for the confusion and I really appreciate all of your help!

[EnterpriseDNA]

Hi @Preston , did the response provided by @Harsh help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

[EnterpriseDNA]

Hi @Preston, we’ve noticed that no response has been received from you since the 6th of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

[sam.mckay]

Amazing work on this Harsh

[EnterpriseDNA]

Hi @Preston, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!