Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
EnrichedUser
Helper III
Helper III

Incorrect Column Total: Row/Grand Totals are good

Hi All,

I have found my most bizzare case in Power BI yet and would love some help. My measure is returning correctly for each row, but fails when calculating the column totals. For sake of debugging my dax, I have replaced my intented measure with a value of 1.

 

What I am seeing is that the column totals are only calculating when there is data in the 'receipts' table. Jan has a one receipt as is returning 1, June has receipts for both keys and is returning two. July to Dec have no receipts and are returning blank. 

 

The expected outcome will correctly sum the column totals (all would be 2 in this example).

 

Matrix Example Revised to sample data.PNG

 

 My model is very simple. It is comprised of a the 'Receipts' table and a fairly standard date table. 

 

Receipts sample:

Eff DateExtended CostQuantitySite_Item_Key
3/13/20239.7100SFA250
3/31/20239.7100SFA250
6/26/20239.327100SFA250
1/12/2023325.566SFA479
4/3/2023927.110SFA479
5/11/2023927.110SFA479
6/22/20238045.9305SFA479

Main sections would be the vTable and column totals

 

 

 

Monthly Inflation (TEST3) = 
VAR SelMo = SELECTEDVALUE( 'Date'[Year Month] )
VAR SelKey = SELECTEDVALUE( Receipts[Site_Item_Key] )
VAR vTable = 
ADDCOLUMNS(
    SUMMARIZE(
        GENERATE(
            VALUES(Receipts[Site_Item_Key]),
            VALUES('Date'[Year Month])
        ),
        Receipts[Site_Item_Key],
        'Date'[Year Month]
        ),
    "@Monthly Inflation", 1 -- PLACEHOLDER/Test
    )
Var Result = 
SWITCH(
    TRUE(),

    ISINSCOPE( Receipts[Site_Item_Key] )
        && ISINSCOPE( 'Date'[Year Month] ), 1, // BASE DATA ROWS

    ISINSCOPE( 'Date'[Year Month] ), // COLUMN TOTALS - HELP
        CALCULATE(
            SUMX( 
                vTable, 
                1 
            ),
            VALUES( Receipts[Site_Item_Key] )
        ),       
    ISINSCOPE( Receipts[Site_Item_Key] ), // ROW TOTALS
        CALCULATE( 
            SUMX( 
                vTable, 
                [@Monthly Inflation] 
            ),
            VALUES( 'Date'[Year Month]  )

        ), // GRAND TOTAL
        SUMX(
            vTable,
            [@Monthly Inflation]
        )
    )
RETURN
Result

 

 

The "1" place holder will be replaced with a measure [Monthly Inflation (R)]

 @amitchandak 

5 REPLIES 5
EnrichedUser
Helper III
Helper III

Monthly Inflation (Test4) = 
VAR SelMo = SELECTEDVALUE('Date'[Year Month])
VAR SelKey = SELECTEDVALUE(Receipts[Site_Item_Key])
VAR vTable =
    ADDCOLUMNS(
        SUMMARIZE(
            GENERATE(
                VALUES(Receipts[Site_Item_Key]),
                VALUES('Date'[Year Month])
            ),
            Receipts[Site_Item_Key],
            'Date'[Year Month]
        ),
        "@Monthly Inflation", 1 // Placeholder
    )
VAR RowTotal =
    CALCULATE(
        SUMX(
            vTable,
            [@Monthly Inflation]
        ),
        ALL('Date'[Year Month])
    )
VAR ColumnTotal = // HELP
    CALCULATE(
        SUMX(
            vTable,
            [@Monthly Inflation] ),
        ALL(Receipts[Site_Item_Key]),
        VALUES('Date'[Year Month])
    )
VAR GrandTotal =
    SUMX(
        vTable,
        [@Monthly Inflation]
    )
RETURN
    IF(
        ISINSCOPE(Receipts[Site_Item_Key]) && ISINSCOPE('Date'[Year Month]),
        1, // Placeholder
        IF(
            ISINSCOPE('Date'[Year Month]),
            ColumnTotal,
            IF(
                ISINSCOPE(Receipts[Site_Item_Key]),
                RowTotal,
                GrandTotal
            )
        )
    )

Newest Revision

Ashish_Mathur
Super User
Super User

Hi,

Your expected result is not clear.  For a sample dataset, show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi, thank you for taking time to review. I just updated the post so that the picture used the same data as the sample and added more detail.

 

Please let me know if there is any more clarify I can provide. Thanks again!

Matrix Example Revised to sample data.PNG

Hi,

Share the download link of the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

I am unfortunally not able to at this time. But the model is extremely simple. 
There is only two tables and both are included below.

Receipts:

Eff DateQuantitySite_Item_Key
3/13/2023100SFA250
3/31/2023100SFA250
6/26/2023100SFA250
1/12/20236SFA479
4/3/202310SFA479
5/11/202310SFA479
6/22/2023305SFA479

 

Date:

 

Date = 
ADDCOLUMNS (
   CALENDAR ( DATE(2023,1,1), DATE(2023,12,31) ),
    "Year", YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "Month No", MONTH ( [Date] ),
    "Quarter", "Q" & CEILING ( MONTH ( [Date] ) / 3, 1 ),
    "Week No", WEEKNUM ( [Date] ),
    "Weekday", FORMAT ( [Date], "dddd" ),
    "Weekday No", WEEKDAY ( [Date], 2 ) + 1,
    "Year Month", FORMAT ( [Date], "MMM yyyy" ),
    "Month Year No", YEAR ( [Date] ) * 100 + MONTH ( [Date] ),
    "Week Year", YEAR ( [Date] ) * 100 + WEEKNUM ( [Date] ),
    "Month Sort", YEAR ( [Date] ) * 10000 + MONTH ( [Date] ),
    "Week Sort", YEAR ( [Date] ) * 10000 + WEEKNUM ( [Date] )
)

 

 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.