Forum Discussion

sivarajan21's avatar
sivarajan21
Icon for Post Prodigy rankPost Prodigy
3 years ago
Solved

Create a stacked bar chart visual by summing a column from two different table

Hi,

 

I have tried so many ways to create this measure, but could not figure out correctly. 

Below is my sample dataset:

Data Table:

DateAcrualSource
01/01/20221Profile
02/01/20222Profile
03/01/20221Direct
07/01/20221Direct

 

Missing Date Table:

DateTarget
04/01/20221
05/01/20221
06/01/20222

 

I need a measure to calculate sum of Acrual in Data table for their corresponding Date and Source. If the the Date is missing in Data table for example Missing Date table contains the Date(04/01/2022,05/01/2022 and 06/01/2022)  that is not present in 

Data table. For those missing dates, we need to sum those Target in Missing Date Table.

If possible, one measure for profile,one measure for Direct and one measure for Target would be greatly appreciated.

 

 

My expected output:

DateAccrualSource
01/01/20221Profile
02/01/20222Profile
03/01/20221Direct
04/01/20221Target
05/01/20221Target
06/01/20222Target
07/01/20221Direct

 

The main reason for creating this measure to use that in a visual as below:

For our case, we can use month instead of Period

 

Can you please guide me on this?

PFA sample file in below link:

Stacked bar chart visual.pbix

 

Thanks in advance

Ahmed lbendlin Ashish_Mathur amitchandak 

 

Interesting Links
Need Help
Show and Tell
Tips and Tricks
  • lbendlin's avatar
    lbendlin
    3 years ago

    Fix this part

     

     

    Get rid of the Month Lookup table, and add a Date column to your Target table.  Can be the first of the month, for example.

6 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago

    You can do this with a simple COALESCE(Actuals, Target)

    • sivarajan21's avatar
      sivarajan21
      Icon for Post Prodigy rankPost Prodigy
      3 years ago

      Hi lbendlin ,

       

      Apologise for the delay in response!

      Thanks for your superb idea and I had worked on this and tested a lot.

      We are very close to the solution, and here is my observation:

      Dax code written was: 

      Accrual kwh =
      COALESCE ( [Profile], [Direct], [Target accrual] )

       

      The dax measures inside COALESCE are as below:

      Profile =
      CALCULATE ( SUM ( Data[Units] ), FILTER ( Data, Data[Source] = "Profile" ) )

       

      Target accrual =
      CALCULATE (
      SUM ( 'Missing Calendar date'[Target Unit] ),
      USERELATIONSHIP ( 'Missing Calendar date'[Date], 'Calendar'[Date] )
      )

       

      Direct =
      CALCULATE ( SUM ( Data[Units] ), FILTER ( Data, Data[Source] = "Direct" ) )

       

      My sample dataset are as below:

      Data table:

      DatePointsSourceUnitsLast UpdateCostDS
      01 March 2023INSE-965Profile410.7420044  1
      02 March 2023INSE-965Profile406.1069946  1
      03 March 2023INSE-965Profile471.0010376  1
      04 March 2023INSE-965Profile306.3860168  1
      05 March 2023INSE-965Profile0  1
      06 March 2023INSE-965Profile436.7469482  1
      07 March 2023INSE-965Profile415.3769531  1
      08 March 2023INSE-965Profile499.9590149  1
      01 February 2023INSE-100Direct50  1
      02 February 2023INSE-100Profile44  1
      03 February 2023INSE-100Invoice20  1
      04 February 2023INSE-100Profile10  1

       

      Missing Calendar date_1

      DB Name - Points IdDateTarget UnitTarget Cost
      INSE-96509 March 20230.8949999955.196775402
      INSE-96510 March 20230.8949999955.196775402
      INSE-96511 March 20230.8949999955.196775402
      INSE-96512 March 20230.8949999955.196775402
      INSE-96513 March 20230.8949999955.196775402
      INSE-96514 March 20230.8949999955.196775402
      INSE-96515 March 20230.8949999955.196775402
      INSE-96516 March 20230.8949999955.196775402
      INSE-96517 March 20230.8949999955.196775402
      INSE-96518 March 20230.8949999955.196775402
      INSE-96519 March 20230.8949999955.196775402
      INSE-96520 March 20230.8949999955.196775402
      INSE-96521 March 20230.8949999955.196775402
      INSE-96522 March 20230.8949999955.196775402
      INSE-96523 March 20230.8949999955.196775402
      INSE-96524 March 20230.8949999955.196775402
      INSE-96525 March 20230.8949999955.196775402
      INSE-96526 March 20230.8949999955.196775402
      INSE-96527 March 20230.8949999955.196775402
      INSE-96528 March 20230.8949999955.196775402
      INSE-96529 March 20230.8949999955.196775402
      INSE-96530 March 20230.8949999955.196775402
      INSE-96531 March 20230.8949999955.196775402
      INSE-10005 February 20230.9556546386.825658
      INSE-10006 February 20230.9556546386.825658
      INSE-10007 February 20230.9556546386.825658
      INSE-10008 February 20230.9556546386.825658

       

      Target Table

      DBName-Point_IdTargetTypeValue TypeValueSource_NumSourceMonth
      INSE-9650Value_03900.374BudgetMarch
      INSE-9651Value_035227.9564BudgetMarch

       

      The above sample dataset can be found in below link:

      COALESCE Function not working.pbix

       

      When I used this measure 'Accrual kwh' in visual it includes only the first argument ' [Profile]' and doesn't include '[Direct]' & '[Target accrual]'. For example, INSE-965 has total of 2946.318(Accrual kwh) Units for Source Profile from 1st March 2023 to 8th March 2023 which shown as in below visual highlighed in red:

      The same is shown in above tabular visual.

      what we wanted to achieve is to fill(sum) the units for missing dates  in Data table with the Missing calendar date table. for we have 1st March 2023 to 8th March 2023 in Data table and from 09 march 2023 to 31st March 2023 is found in Missing Calendar date table.

       

      The expected output should be as below:

      MonthInvoiceTargetAccrual kwhTarget accrualDirectProfile
      February20 107.823.825054
      March 900.372966.920.58 2946.32

       

      That is 'Accrual kwh' measure will contain sum of values in order of

      February -- >54 (Profile) + 50 (Direct) + 3.82 (Target Accrual ) =107.82

      March ---> 2946.32 (Profile) + BLANK (Direct) + 20.58 (Target Accrual ) = 2966.9

       

      I am confused in using COALESCE function.

      Could you please help me to solve & whether we can use this or some other dax?

       

      Thanks in advance.

       Ahmedx   lbendlin Ashish_Mathur  grantsamborn Greg_Deckler amitchandak onurbmiguel_ VijayP Idrissshatila 

       

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        3 years ago

        Fix this part

         

         

        Get rid of the Month Lookup table, and add a Date column to your Target table.  Can be the first of the month, for example.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    In the Target table as well ensure that there is a third column with the same heading as is there in the first table.  In the Query Editor, append the 2 tables.

  • sivarajan21's avatar
    sivarajan21
    Icon for Post Prodigy rankPost Prodigy
    3 years ago

    Hi Team,

     

    Thanks for all your responses given till now.

    As I am not getting any solution, I will close this query.