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
vpsoini
Helper I
Helper I

Matrix visual mystery on running totals...

Hello.

 

I'm really puzzled with matrix visual trying to show the running total value over time.  In the attached picture you can see two similar matrix visuals (with slighlty different measure as value) and a graph visual about same value.  My running total (inventory change over time) is correctly calculated, but i also needed the value to take into account the start value, which was not zero (here it was 6)

 

The upper matrix visual in the picture shows [ground value] + [running total] and the lower matrix only shows [running total]. The graph visual shows [ground value] + [running total].  As comparison there's a snip from excel showing the inventory evolution per dates. 

 

The big mystery for me is the upper matrix, which should show the inventory over time (same as the graph visual). For some reason, it shows only correct value on dates, where the running total actually changes (except on 24.1. when it has the wrong number and 15.1. when it changes without obvious reason?). In between the "change dates" it shows the running total change (missing the start value, which is here 6).

 

The running total is calculated fron 2 tables (sales and purchases) and the variation is correct in lower matrix. Ground value is taken from items-table, and it has only one value per item (not date related). These tables are linked with item numbers between each others.

 

The base question is, why is the same measure as value working OK in graph visual, but not in matrix visual? A bug?

 

vpsoini_0-1641377842784.png

 

Any help is highly appreciated again... 😊

 

1 ACCEPTED SOLUTION

Hi @vpsoini ,

 

Believe your issue is because you don't have consecutive dates on your calculation for a specific item, altough all your products at some point have values making your dates consecutive for a specific item that is not true so your calculation breaks when you have no results.

 

  • Create a calendar table with unique values and starting in 1st January your minimum year and ending at 31st December your maximum year
  • Change your measure to:
RunningTotal =
SUM ( Items[Inventory] )
    + CALCULATE (
        SUM ( 'Item ledger'[Quantity] ),
        FILTER (
            ALL ( 'calendar'[Date] ),
            'calendar'[Date] <= MAX ( 'calendar'[Date] )
        )
    )

 

Small model attach.

MFelix_0-1641565383063.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

3 REPLIES 3
MFelix
Super User
Super User

Hi @vpsoini ,

 

This has to do with context and the way you are calculating your measure.

 

Since the 6 does not have any dates it is being constantly added to your calculations when you get to values with 6. In matrix and other visualizations you need to be carefull because adding fields changes the context of the measures and when you have calculations like the begining inventory this can cause errors.

 

Can you please share the measure you are using for this calculation?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix 

 

The measure looks like this:

RunningTotal = MAX(Items[Inventory]) + CALCULATE(SUM('Item ledger'[Quantity]),FILTER(ALLSELECTED('Item ledger'),'Item ledger'[Posting_Date] <= MAX('Item ledger'[Posting_Date])))

 

Where Items - table has the item number, name and base value (inventory), where Item ledger has Item number, Quantity (change +/-) and Posting date and these tables are linked with Item number fields

 

Hi @vpsoini ,

 

Believe your issue is because you don't have consecutive dates on your calculation for a specific item, altough all your products at some point have values making your dates consecutive for a specific item that is not true so your calculation breaks when you have no results.

 

  • Create a calendar table with unique values and starting in 1st January your minimum year and ending at 31st December your maximum year
  • Change your measure to:
RunningTotal =
SUM ( Items[Inventory] )
    + CALCULATE (
        SUM ( 'Item ledger'[Quantity] ),
        FILTER (
            ALL ( 'calendar'[Date] ),
            'calendar'[Date] <= MAX ( 'calendar'[Date] )
        )
    )

 

Small model attach.

MFelix_0-1641565383063.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Europe Fabric Conference

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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