Forum Discussion

swebb's avatar
swebb
Helper II
3 years ago

Custom visual with multiple DAX commands

All,

 

I have a custom financial report structure with measures for each entry. One of the issues I'm running in to is I can't update some of the fields, and for the life of me I cannot figure out why. I can update other fields just fine. See below for screen shots.

 
Individual DAX measures:
Working:
Subscription = SUMX(
    FILTER(
        'Consolidated',
        'Consolidated'[accountNumber] IN {410000}
    ),
    'Consolidated'[TotalExchangeRateAmount]
)
 
Not working:
EBITDA Interest = SUMX(
    FILTER(
        'Consolidated',
        'Consolidated'[accountNumber] IN {730000, 740000, 740010}
    ),
    'Consolidated'[TotalExchangeRateAmount]
)
 
For the Structure of the Report:
This gets applied to the Matrix visualization value field
Financial Value =
SWITCH(
    SELECTEDVALUE('Structure'[Main Struct - Copy]),
    "Subscription", FORMAT([Subscription], "Currency"),
    "Maintenance", FORMAT([Maintenance], "Currency"),
    "Total Recurring", FORMAT([Total Recurring], "Currency"),
    "License", FORMAT([License], "Currency"),
    "Services", FORMAT([Services], "Currency"),
    "Other Rev.", FORMAT([Other Rev.], "Currency"),
    "People", FORMAT([People], "Currency"),
    "Salary", FORMAT([Salary], "Currency"),
    "Taxes and Benefits", FORMAT([Taxes and Benefits], "Currency"),
    "Commission", FORMAT([Commission], "Currency"),
    "Bonus", FORMAT([Bonus], "Currency"),
    "Automobile", FORMAT([Automobile], "Currency"),
    "Other People", FORMAT([Other People], "Currency"),
    "Recruiting", FORMAT([Recruiting], "Currency"),
    "Contract Services", FORMAT([Contract Services], "Currency"),
    "Microsoft ISV", FORMAT([Microsoft ISV], "Currency"),
    "Consulting", FORMAT([Consulting], "Currency"),
    "Commission - Partners", FORMAT([Commission - Partners], "Currency"),
    "Trade Show", FORMAT([Trade Show], "Currency"),
    "User Conference", FORMAT([User Conference], "Currency"),
    "Hosting", FORMAT([Hosting], "Currency"),
    "Travel & Ent.", FORMAT([Travel & Ent], "Currency"),
    "Office", FORMAT([Office], "Currency"),
    "Technology", FORMAT([Technology], "Currency"),
    "Computer", FORMAT([Computer], "Currency"),
    "Software", FORMAT([Software], "Currency"),
    "Dues and Subscr.", FORMAT([Dues and Subscrip], "Currency"),
    "Other Technology", FORMAT([Other Technology], "Currency"),
    "Accounting", FORMAT([Accounting], "Currency"),
    "Transaction", FORMAT([Transaction], "Currency"),
    "Marketing", FORMAT([Marketing], "Currency"),
    "Professional Services", FORMAT([Professional Services], "Currency"),
    "Intercompany Mgmt. Fee", FORMAT([Intercompany Mgmt. Fee], "Currency"),
    "Other", FORMAT([Other], "Currency"),
    "Misc.", FORMAT([Misc.], "Currency"),
    "Interest", FORMAT([Interest], "Currency"),
    "Taxes", FORMAT([Taxes], "Currency"),
    "Amortization", FORMAT([Amortization], "Currency"),
    "Depreciation", FORMAT([Depreciation], "Currency"),
    "Gain/Loss on Exchange", FORMAT([Gain/Loss on Exchange], "Currency"),
    "Intercompany", FORMAT([Intercompany], "Currency"),
    "Total Expenses", FORMAT([Total Expenses], "Currency"),
    "Net Operating Income", FORMAT([Net Operating Income], "Currency"),
    "Total Revenue", FORMAT([Total Revenue], "Currency"),
    "EBITDA Interest", FORMAT([EBITDA Interest], "Currency"),
    "EBITDA Taxes", FORMAT([EBITDA Taxes], "Currency"),
    "EBITDA Depreciation", FORMAT([EBITDA Depreciation], "Currency"),
    "EBITDA Amortization", FORMAT([EBITDA Amortization], "Currency"),
    "EBITDA", FORMAT([EBITDA], "Currency"),
    "Plus:Addbacks", FORMAT([Plus: Addbacks], "Currency"),
    "Plus:Intercompany", FORMAT([Plus: Intercompany], "Currency"),
    "Plus:Transaction", FORMAT([Plus: Transaction], "Currency"),
    "Plus:Gain/Loss on Exchange", FORMAT([Plus: Gain/Loss on Exchange], "Currency"),
    "Adj. EBITDA", FORMAT([Adj. EBITDA], "Currency"),
    "Less Capitalized Commissions", FORMAT([Less Capitalized Commissions], "Currency"),
    "Less Capex", FORMAT([Less Capex], "Currency"),
    "Less Capitalized Software", FORMAT([Less Capitalized Software], "Currency"),
    "FCF", FORMAT([FCF], "Currency")
    )
   
Before and after changing the DAX to one of the non-working value fields:

 


When trying to apply the DAX measure to the EBITDA Interest I just get blanks across all:

 

 I verified the table data actually has data in the TotalExchangeRateAmount column, and that there are corresponding dates for all the months, or at least some of the months. Just not sure what I'm doing wrong. I can provide additional information if needed. 

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    swebb , The measure seems fine to me, unless there is some issue with spaces or special characters.

    Just create a small measure for those values for any such issue.

     

    Also, when everything is formatted to currency, Why are you formatting and creating a text measure, you can format it once using measure tools

    • swebb's avatar
      swebb
      Helper II
      3 years ago

      The values were showing up wonky when on the matrix visualization, even though I limited the amount of decimal values. I went through and did the FORMAT and it corrected it. Not really sure why. 

  • swebb's avatar
    swebb
    Helper II
    3 years ago

    Greg_Deckler Any thoughts on this? I can provide additional information as needed. TL;DR, have a custom formatted Matrix visualization with DAX measures for each entry, most of them work, some of them do not. Checked formatting, checked values in corresponding table, all seems to look good, but cannot get values to populate in any of the blank rows now.