New Enterprise DNA Initiatives

Help with Display Number of Days in Graph

DAX Calculations
#1

Hello,

I have a report that has a table that I need to get into a bar graph. I have a field called “Lead to Customer Days” in this table, this field displays in the table just fine. But when I change the table to any type of bar graph the number of that field changes.

Example would be in the attached report… look at customer TESLA, this has a number of -250 which is correct. But when I change this table to a bar graph that number changes to 2417, ONLY when I have no filters selected in the slicers. If I have this graphed and I have it filtered down to only show TESLA then it properly shows -250. I am sure its an issue with my measure for “Earliest Lead Date” however I cannot figure it out. Help!

image
image1153×589 33.4 KB

image
image1249×596 36.4 KB

Lead to Customer.pbix (1.6 MB)

#2

Hi there,

The numbers do not change because you change from Table to graph, but because you drop the date (which is necessary for the measure calculation) out of the graph. (see attachment to see the difference between a graph with and without data included).

The first graph is still not perfect, because:
(1) all customers have different colors
(2) small number seem to be omitted.

but I hope it helps you!

Lead to Customer.pbix (1.6 MB)

Kr,
Rens

#3

Thanks for you reply, neither of the graphs you provided solve my problem. If you look at TESLA in either of them graphs they do not have the number -250 (like they do in the table) which is the correct amount for the “Lead to Customer Days” field. Unless I am missing something entirely? Do I need to redo my measure?

#4

Hi Preston,

Indeed, the solution is not complete. I wanted to show you that leaving the date out (graph 2) leads to
wrong data for some customers (e.g. ZION, WEASLER) but correct data for other customers (graph 1). I hereby want to conclude that changing visual (from table to graph) does not distort the data, but omitting the date does.
However, what is indeed weird is that a lot of customers are omitted.

Sorry that I cannot help you any further!

Good luck!

#5

Ok so do you have any ideas for how I can display that field in a graph? Looking for work arounds here to get that field graphed some how…

#6

Hi Preston, I tried but did not find a solution. I hope somebody else (or your own research) might provide a solution here.

Kind regards,
Rens

#7

Hi @Preston,

To generate the same results as shown in the Table visual, you have to give the Bar chart the same filter context. Give this measure a go.

Leads v2 = 
VAR vTable =
    ADDCOLUMNS(
        SUMMARIZE('Lead', AR_Customer[DateEstablished], Company[comp_mas_customerno] ),
        "@ErLeadDate", [Earliest Lead Date],
        "@LeadCustDays", [Lead to Customer Days]
    )
RETURN

SUMX( vTable, [@LeadCustDays] )

With this result.

image
image1248×625 46.2 KB

I hope this is helpful.

1 Like