Forum Discussion

zbhargav15's avatar
zbhargav15
Regular Visitor
3 years ago
Solved

Creating a custom Percentage column in Direct Query mode

I have a dataset imported from my DB2 table which looks like the following:

AccountIDCategoryAmount
ABCPen100
ABCPaper20
ABCPencil80
ABCTotal200
DEFPen100
DEFPaper0
DEFPencil0
DEFTotal100

 

I need to display just the Total row for every Account in a table visual but need to create 3 columns- Pen % , Pencil %, Paper% which depict the percentage spent on that item by that particular account ID.


I was able to create a query using DAX when i used Excel to import this data:
Pen Percentage = IF('stationary'[Category]="Total", CALCULATE(SUM('stationary'[Amount]),FILTER('stationary', 'stationary'[Category]<>"Total" && 'stationary'[AccountID] = EARLIER('stationary[AccountID]) && 'stationary'[Category]="Pen")) / 'stationary'[Amount] * 100 & "%", BLANK())


This query worked for me but it doesn't when i use DirectQuery mode. How can i achieve the same result?

Need Help
  • edhans's avatar
    edhans
    3 years ago

    I don't know you are going to be able to do this with Direct Query. It has limitations, but if you simplify your DAX it might help.

     

    Try the following measure. This will return the total percents correctly:

    Percent of Account ID = 
VAR varCategory = SELECTEDVALUE('Sales'[Category])
VAR varAccountID = SELECTEDVALUE('Sales'[AccountID])
VAR varTotal = 
    CALCULATE(
        [Sales Amount],
        ALLEXCEPT('Sales', 'Sales'[AccountID])
    )
VAR varCategorySales =
    CALCULATE(
        [Sales Amount],
        'Sales'[AccountID] = varAccountID,
        'Sales'[Category] = varCategory
    )
VAR Result = DIVIDE(varCategorySales, varTotal, BLANK())
RETURN
    Result

     

    Then for just the Pen percent, use this:

    Pen Percent = 
CALCULATE(
    [Percent of Account ID],
    KEEPFILTERS( Sales[Category] = "Pen" )
)

    I used a matrix, and I got rid of the totals in Power Query. Let the visual give you the totals.

     

    Get the basics working first and see if the engine can generate the SQL code necessary for Direct Query. Then you can add IF statements or ISINSCOPE statements to get the visual like you want.

    However, as a rule, I strongly advise against Direct Query. It has it uses, but import is where DAX works best, and if this were a multi-million record dataset, I don't care what DAX you write that gives you the results you want, it will be 3-10 times slower than an import model.

1 Reply

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    3 years ago

    I don't know you are going to be able to do this with Direct Query. It has limitations, but if you simplify your DAX it might help.

     

    Try the following measure. This will return the total percents correctly:

    Percent of Account ID = 
VAR varCategory = SELECTEDVALUE('Sales'[Category])
VAR varAccountID = SELECTEDVALUE('Sales'[AccountID])
VAR varTotal = 
    CALCULATE(
        [Sales Amount],
        ALLEXCEPT('Sales', 'Sales'[AccountID])
    )
VAR varCategorySales =
    CALCULATE(
        [Sales Amount],
        'Sales'[AccountID] = varAccountID,
        'Sales'[Category] = varCategory
    )
VAR Result = DIVIDE(varCategorySales, varTotal, BLANK())
RETURN
    Result

     

    Then for just the Pen percent, use this:

    Pen Percent = 
CALCULATE(
    [Percent of Account ID],
    KEEPFILTERS( Sales[Category] = "Pen" )
)

    I used a matrix, and I got rid of the totals in Power Query. Let the visual give you the totals.

     

    Get the basics working first and see if the engine can generate the SQL code necessary for Direct Query. Then you can add IF statements or ISINSCOPE statements to get the visual like you want.

    However, as a rule, I strongly advise against Direct Query. It has it uses, but import is where DAX works best, and if this were a multi-million record dataset, I don't care what DAX you write that gives you the results you want, it will be 3-10 times slower than an import model.