Totalling Issue - Maybe!

Pete673 · February 24, 2021, 4:30pm

Hi guys
I am working on a pbix to analyze repair work in a car workshop. A Doc (invoice or credit note) can consist of multiple repairs, so I need counts for Repairs and docs.

Repair count is basically the number of repair lines on the invoice and in terms of DAX fairly straight forward to work through (albeit with some hefty power query first so as not sum invalid lines).

However Doc count is proving a little elusive. see below screen shot:

The highlighted rows are showing correctly; ie Repair count for each line and only 1 doc
This was difficult in itself with the standard running total patterns based around EARLIER either too slow or just not working and throwing errors. I overcame this by identifying the largest LastLogMagic (unique identifier for each row), then checked this against the current row. If matched then 1 otherwise zero. However I cannot get the measure to provide totals.

I am working with 2 measures during dev, just to keep things simple; one for the line calc then another measure to do the totalling which is applied to the visual. Line Amount is DocCountPrep and totals is DocCount. The Measure DocCount however is not providing totals as you can see above.See DAX below. I applied the same principles for RepairCount and all is fine - standard DAX pattern with SUMX(Summarize…),

So now I’m totally befuddled and not sure where to go next. It must be the MaxLog variable that is causing the problem as the only thing that is different between RepairCount and DocCount.

Help greatly appreciated

Pete

DocCountPrep =
VAR ServiceDoc = LOOKUPVALUE(
    DOCStats[MainCount],
    DOCStats[InvoiceNumber],
    max( SO_Fact[InvoiceNumber] ),
    DOCStats[MainType],
    max( RepairCatgories[RepCat Desc] )
) 
VAR MaxLog = CALCULATE(
    max( SO_Fact[LastLogMagic] ),
    Filter(
        ALLSELECTED( SO_Fact ),
        SO_Fact[InvoiceNumber] = max( SO_Fact[InvoiceNumber] )
    )
) 
VAR DocCount = Switch(
    TRUE(),
    Max( RepairCatgories[RepCat Desc] ) = "Service AND MOT", if(
        max( SO_Fact[Category] ) = "Labour", ServiceDoc,
        Blank()
    ),
    Max( RepairCatgories[RepCat Desc] ) = "Service", if(
        max( SO_Fact[Category] ) = "Labour", ServiceDoc,
        Blank()
    ),
    Max( RepairCatgories[RepCat Desc] ) = "MOT", if(
        max( SO_Fact[Category] ) = "Labour", ServiceDoc,
        Blank()
    ),
    Max( RepairCatgories[RepCat Desc] ) = "Warranty", if(
        max( SO_Fact[Category] ) = "Labour", ServiceDoc,
        Blank()
    ),
    Max( RepairCatgories[RepCat Desc] ) = "Internal - New", if(
        max( SO_Fact[Category] ) = "Labour", ServiceDoc,
        Blank()
    ),
    Max( RepairCatgories[RepCat Desc] ) = "Internal - Used", if(
        max( SO_Fact[Category] ) = "Labour", ServiceDoc,
        Blank()
    ),
    Max( RepairCatgories[RepCat Desc] ) = "Internal - Other", if(
        max( SO_Fact[Category] ) = "Labour", ServiceDoc,
        Blank()
    ),
    //Max( RepairCatgories[RepCat Desc] ) = "Upsell" , LOOKUPVALUE( DOCStats[UpSellJob] , DOCStats[InvoiceNumber] , max( SO_Fact[InvoiceNumber] ) ) , // Max( RepairCatgories[RepCat Desc] ) = "Tyres" , LOOKUPVALUE( DOCStats[TyreJob] , DOCStats[InvoiceNumber] , max( SO_Fact[InvoiceNumber] ) ) , //Max( RepairCatgories[RepCat Desc] ) = "Coll AND Del" , LOOKUPVALUE( DOCStats[CollDelJob] , DOCStats[InvoiceNumber] , max( SO_Fact[InvoiceNumber] ) ) , // Max( RepairCatgories[RepCat Desc] ) = "Courtesy Car" , LOOKUPVALUE( DOCStats[CourtesyJob] , DOCStats[InvoiceNumber] , max( SO_Fact[InvoiceNumber] ) ) , // Max( RepairCatgories[RepCat Desc] ) = "Bodyshop Labour" , LOOKUPVALUE( DOCStats[BodyJob] , DOCStats[InvoiceNumber] , max( SO_Fact[InvoiceNumber] ) ) , // Max( RepairCatgories[RepCat Desc] ) = "Smart Repair" , LOOKUPVALUE( DOCStats[SmartDoc] , DOCStats[InvoiceNumber] , max( SO_Fact[InvoiceNumber] ) ) , // Max( RepairCatgories[RepCat Desc] ) = "Bodyshop Paint" , LOOKUPVALUE( DOCStats[PaintDoc] , DOCStats[InvoiceNumber] , max( SO_Fact[InvoiceNumber] ) ) , // Max( RepairCatgories[RepCat Desc] ) = "Other Labour" , LOOKUPVALUE( DOCStats[OtherJob] , DOCStats[InvoiceNumber] , max( SO_Fact[InvoiceNumber] ) ) , BLank() )
    Return docCount * if(
        sum( SO_Fact[Gross Sales] ) = 0,
        0,
        1
    ) * if(
        max( SO_Fact[LastLogMagic] ) = MaxLog,
        1,
        0
    )

And the Doc count measure:
DocCount =
VAR MaxLog = CALCULATE(
    max( SO_Fact[LastLogMagic] ),
    Filter(
        ALLSELECTED( SO_Fact ),
        SO_Fact[InvoiceNumber] = max( SO_Fact[InvoiceNumber] )
    )
) 
VAR test = SUMX(
    SUMMARIZE(
        SO_Fact,
        RepairCatgories[RepCat Desc],
        SO_Fact[Menu],
        SO_Fact[Category],
        SO_Fact[InvoiceNumber],
        SO_Fact[ProductCode],
        SO_Fact[LastLogMagic]
    ),
    [DocCountPrep] * if(
        max( SO_Fact[LastLogMagic] ) = MaxLog,
        1,
        0
    )
) Return Test

Greg · February 24, 2021, 7:19pm

Hi @Pete673.

To help us further analyze your current state and visualize your issue, could you please provide as many as you can of:

Your work-in-progress PBIX file, using sanitized data if necessary
- here’s @BrianJ’s YouTube video on how to anonymizing data in a Power BI file: https://www.youtube.com/watch?v=VmWD7Ayw_NI
Your dataset as an Excel file (again, sanitized if necessary)
A detailed mock-up (marked-up screenshot or Excel file) of your desired outcome.

Greg

EnterpriseDNA · February 25, 2021, 1:17am

Hi @Pete673, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

Use the forum search to discover if your query has been asked before by another member.
When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.

Use the proper category that best describes your topic
Provide as much context to a question as possible.
Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

Pete673 · February 25, 2021, 8:54am

Hi guys
Watered down PBIX attached; Issue is DocCounts not totalling.

Interestingly the column does total when a DepartmentCode is filtered; but not when it isn’t.
So now I’m even more confused.

PS: the docstats table is due a revamp with a pivot routine, then rather than use lookups, I’ll use sum with filters. So apologies for it looking a bit messy. Doesn’t alter the problem though.

BTW; Always learn something new on this forum that’s got nothing to do with the issue I’m managing!
Brian’s anonymising data video was v interesting and simple

Look forward to a fix

PeteAfterSalesEDNA.pbix (311.8 KB)

Greg · February 25, 2021, 2:07pm

Hi @Pete673. I think what you mean by “DocCounts not totalling” is that the value returned is not what you expect; I do get a total returned for all cases, although sometimes it is zero. My first thoughts would be to:

replace your sums of “naked” columns from your fact table with dedicated measures, e.g.,

Gross Sales M = SUM( SO_Fact[Gross Sales] )

remove all measures from the visual physical table and ensure that my table displays and responds as desired for filter selections
then review the Fix Incorrect Totals forum post and adjust the virtual tables used in your measures as necessary to mimic the physical table before re-adding the measures to the visual.

Good luck.
Greg

Pete673 · February 26, 2021, 8:46am

Hi Greg
Still struggling with the totalling here. Revised PBIX attached, having followed the advice in your previous post.
When filtering on a [Department code] and [RepCat Desc] eg [Dept W] and [Repcat Desc] Warranty, it gives a grand total of 7 which is correct - 7 docs with 11 repairs across the 7 Docs.
But when click on Select All Department codes we lose the grand total.

(There is an issue with the 7 and no DocCuunt on anything but Warranty but I think this is PQ thing to fix and an issue with this data extract).

help!
PeteAfterSalesEDNA.pbix (335.1 KB)

Greg · February 26, 2021, 4:26pm

HI @Pete673. I’d suggest you resolve your data extract issues first (or possibly create a small sample PBIX which illustrates your issue) before we proceed further; the first place I look when I encounter DAX difficulties are the data received from my data sources, otherwise it is not unlikely to go down a rabbit-hole and expend significant time on a non-existent issue.
Greg

Pete673 · February 26, 2021, 4:55pm

Hi Greg
yes you’re probably right. I have been doing some work on the data today and it seems to be bearing fruit.
Let’s close post down and I’ll crack on.
Apologies for wasting your time

Pete

Greg · February 26, 2021, 6:41pm

No apologies necessary … no worries at all … one of the best things about my former engineering career was instilling that you must always consider “do nothing” as a viable option to be evaluated alongside the others.
Greg