Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Display total count in Power BI line chart

Hi,   How to display total count value in end of the line chart. In below line chart I have count for 6-3447,12-443,18-1187,24-396.   For Example, 3447+443+1187+396 = 5473     Regards, ...
Need Help
  • jtownsend21's avatar
    jtownsend21
    7 years ago

    I think I got it figured out. My results won't match your data set because I had to recreate the sample data. It wouldn't let me copy and paste it from your doc because the size was too large. No worries though, I think I found a solution for you. First create a column as follows (you can add as many groups as you like, I only went to the last 24 months): 

    6 Month Increments = 
VAR _DateDiff = 
    DATEDIFF(TODAY(),Sheet1[ModifiedDate],DAY)

RETURN
SWITCH(
    TRUE(),
    _DateDiff > (-180), "1. Last 6 Months",
    _DateDiff > (-360), "2. Last 12 Months",
    _DateDiff > (-540), "3. Last 18 Months",
    _DateDiff > (-720), "4. Last 24 Months",
    "5. Older"
)

    Then create either use the quick measure creation tool I showed before, or just write one that mirrors the following: 

    Count of CommunityID running total in 6 Month Increments = 
CALCULATE(
	COUNTA('Sheet1'[CommunityID]),
	FILTER(
		ALLSELECTED('Sheet1'[6 Month Increments]),
		ISONORAFTER('Sheet1'[6 Month Increments], MAX('Sheet1'[6 Month Increments]), DESC)
	)
)

    This will give you the following result when you put them in your chart: 

    Here is a PBIX with the solution in it as well in case it helps. 

jtownsend21's avatar
jtownsend21
Responsive Resident
7 years ago

Anonymous Can you share some sample data or your PBIX? 

 

How are you calculating "Timeline"? Is that a field in your data? 

 

I am still fairly certain the running total quick measure should work for you. If you can share sample data or PBIX I would be happy to play with it and see if I can get something working for you. 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi,

 

Thanks for your reply.

Here I have attched the source data, I need the Community count for last 6Month,12Month...,24Month afterwards finally i have to show total count.

 

I have created the column as Month category its have values as 6,12,18,24.

MonthCategory = VAR six =1.* (max(tbl_OverallInventoryDetails[ModifiedDate])-tbl_OverallInventoryDetails[6Month])

VAR twelve =1.* (max(tbl_OverallInventoryDetails[ModifiedDate])-tbl_OverallInventoryDetails[12Month])
VAR eighteen =1.* (max(tbl_OverallInventoryDetails[ModifiedDate])-tbl_OverallInventoryDetails[18Month])
VAR twentyfour =1.* (max(tbl_OverallInventoryDetails[ModifiedDate])-tbl_OverallInventoryDetails[24Month])

RETURN

if(MAX(tbl_OverallInventoryDetails[ModifiedDate]) - tbl_OverallInventoryDetails[ModifiedDate] <six,6,
if(MAX(tbl_OverallInventoryDetails[ModifiedDate]) - tbl_OverallInventoryDetails[ModifiedDate] <twelve,12,
if(MAX(tbl_OverallInventoryDetails[ModifiedDate]) - tbl_OverallInventoryDetails[ModifiedDate] <eighteen,18,
if(MAX(tbl_OverallInventoryDetails[ModifiedDate]) - tbl_OverallInventoryDetails[ModifiedDate] <twentyfour,24
))))

 

In above line chart i got count for month wise but am expecting to get the running total.

https://avacorp1-my.sharepoint.com/:x:/g/personal/yuvaraj_g_avasoft_com/EcTJQbXlzftCmhQfgINmWSgBG85vBL2837HLXf8XcKb7nA?e=WCy1tA

 

Regards,

Yuvaraj

  • jtownsend21's avatar
    jtownsend21
    Responsive Resident
    7 years ago

    I think I got it figured out. My results won't match your data set because I had to recreate the sample data. It wouldn't let me copy and paste it from your doc because the size was too large. No worries though, I think I found a solution for you. First create a column as follows (you can add as many groups as you like, I only went to the last 24 months): 

    6 Month Increments = 
VAR _DateDiff = 
    DATEDIFF(TODAY(),Sheet1[ModifiedDate],DAY)

RETURN
SWITCH(
    TRUE(),
    _DateDiff > (-180), "1. Last 6 Months",
    _DateDiff > (-360), "2. Last 12 Months",
    _DateDiff > (-540), "3. Last 18 Months",
    _DateDiff > (-720), "4. Last 24 Months",
    "5. Older"
)

    Then create either use the quick measure creation tool I showed before, or just write one that mirrors the following: 

    Count of CommunityID running total in 6 Month Increments = 
CALCULATE(
	COUNTA('Sheet1'[CommunityID]),
	FILTER(
		ALLSELECTED('Sheet1'[6 Month Increments]),
		ISONORAFTER('Sheet1'[6 Month Increments], MAX('Sheet1'[6 Month Increments]), DESC)
	)
)

    This will give you the following result when you put them in your chart: 

    Here is a PBIX with the solution in it as well in case it helps. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi jtownsend21,

    Thanks for your reply. We almost near to this i need one thing, is that possibile to show the total count as Full Inventory.

    The Full Inventory Contains the total count (6+12+18+24 months) 

     

    Regards,

    Yuvaraj

  • jtownsend21's avatar
    jtownsend21
    Responsive Resident
    7 years ago

    The item showing "last 24 months" in my graph would have the number you are asking for.

     

    Do you mean that you need it to show the labels?  

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Yes am expecting that one.