Forum Discussion

mmahoney045's avatar
mmahoney045
Regular Visitor
2 years ago
Solved

Create Measure From Virtual Table

Hello,

 

I am attempting to create a measure that will return the distinct count of a specific type of customer (New Tank Distributor) that is set up in any given year. After attempting simple measure and calculated table approaches, I believe the only solution that will work for my situation is a virtual table(s) that is then aggregated down to the single scalar value: the sum or count of New Tank Distrubtors over any filtered period of time. 

 

Here are the two variable virtual tables I've create which get me very close. Only thing I am missing is how to produce a single value for New Tank Distributor instead of a row that contain's its label & count. Any advice how to rework the second VAR to produce a scalar value instead of table? 

 

Note: I label each "distributor type" in the first virtual table because once I figure out how to create this measure, I will reproduce it for the other distrubutor types as well.

 

New Tank Distributor = 

VAR _summarytable =
        SUMMARIZECOLUMNS(
            'table'[Customer],
            'table'[InvoiceDate],
            "CY Tank Sales", CALCULATE(sum('table'[tank revenue])),
            "CY All Sales", [Sales ALL Total],
            "Previous 2 Years All Sales", CALCULATE([Sales ALL Total], dateadd('table'[InvoiceDate].[Date],-2,YEAR)) + CALCULATE([Sales ALL Total], dateadd('table'[InvoiceDate].[Date],-1,YEAR)),
            "Previous 2 Years Tank Sales", CALCULATE([Sales ALL Total], 'Product Hierarchy'[Product Category] = "Tanks", dateadd('table'[InvoiceDate].[Date],-2,YEAR)) + CALCULATE([Sales ALL Total],'Product Hierarchy'[Product Category] = "Tanks", dateadd('table'[InvoiceDate].[Date],-1,YEAR)),
            "Distributor Type",
            SWITCH(
                TRUE(),
                AND(CALCULATE([Sales 2YRS Prior], 'Product Hierarchy'[Product Category] = "Tanks") < 0.01, CALCULATE([Sales ALL Total], 'Product Hierarchy'[Product Category] = "Tanks") > 0), "New Tank Distributor",
                AND([Sales 2YRS Prior] < 0.01, [Sales ALL Total] > 0),"New Customer",
                OR(CALCULATE([Sales 2YRS Prior], 'Product Hierarchy'[Product Category] = "Tanks") > 0, CALCULATE([Sales ALL Total], 'Product Hierarchy'[Product Category] = "Tanks") > 0), "Existing Tank Distributor",
                OR([Sales 2YRS Prior] > 0, [Sales ALL Total] > 0),"Existing Customer","Potential Customer")
        )

VAR _aggtable =
        FILTER(
            GROUPBY(
                _summarytable,
                [Distributor Type],
                "Number Of Customers", COUNTX( CURRENTGROUP(),1)), [Distributor Type]="New Tank Distributor")
 
RETURN
_aggtable 
 
(_aggtable produces a single count or sum of New Tank Distributors)
Need Help
  • Daniel29195's avatar
    Daniel29195
    2 years ago

    well, you can t use summarizecolumns in measure 

    try using addccolumns ( summarize ( .. )   instead .

     

    and for the return, just return   aggregation without the {  } .

    the { }  were only needed for the query not to be used in the measure. 

     

    hope this makes sense. 

    mmahoney045 

14 Replies

  • Daniel29195's avatar
    Daniel29195
    Community Champion
    2 years ago

    mmahoney045 

     

    to return a scalar value from the second table , assuming that this returns only one row : 

    VAR _aggtable =
    selectcolumns(,
            FILTER(
                GROUPBY(
                    _summarytable,
                    [Distributor Type],
                    "Number Of Customers", COUNTX( CURRENTGROUP(),1)
                ),
            [Distributor Type]="New Tank Distributor"
            ),
            [Number Of Customers]
            )

     

     

     

    if it returns multiple rows, then you need to either filter to one row, or to use one of the iterators base on your business logic : 
    sumx , maxx, minx, . ..

     

     

    let me know if thelps. 

     


                   

    • mmahoney045's avatar
      mmahoney045
      Regular Visitor
      2 years ago

      Thanks for the response!

       

      Your solution does help to return a single row and column, which is the closest I've come so far. However it does not appear it is a pure scalar value because I cannot throw it in a card to display the single value on my dashboard. 

       

      Is it possible to run one of the aggregator functions over to entire result of the second virtual table?

       

       

      Thank you,

       

      Matt

       

      • Daniel29195's avatar
        Daniel29195
        Community Champion
        2 years ago

        you can use maxx or minx  if you are sure that you have 1 value . or you have multiple values all the same. mmahoney045