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. 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi,

In below line chart, i have to show the running total count. For example, in 6 month the count is 2369 so while show the count for 12 month it should be add the 6 month count total as well. Simillarly for 18,24 month.

Expected result:

for 6 -2369

     12 - 2369+377 = 2746

     18 - 2746+810 = 3556

     24 - 3556+211 = 3767

Finally it should show the total as 3767.

 

Regards,

Yuvaraj

  • jtownsend21's avatar
    jtownsend21
    Icon for Responsive Resident rankResponsive 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
        Icon for Responsive Resident rankResponsive 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.