DAX Questions

[YYlee]

Question 1:

consol Amount =

VAR SelectedCurrency =

    SWITCH(

        TRUE(),

        SELECTEDVALUE(Revenue[Location]) = "Group" || ISBLANK(SELECTEDVALUE(Revenue[Location])), "USD",

        SELECTEDVALUE(Revenue[Location]) = "Hong Kong", "HKD",

        SELECTEDVALUE(Revenue[Location]) = "Singapore", "SGD",

        SELECTEDVALUE(Revenue[Location]) = "Shanghai", "CNY",

        BLANK()

    )

VAR Amount =

    SWITCH(

        TRUE(),

        SelectedCurrency = "USD",

            -- Use TREATAS to map Revenue[Location] to Consolidated (2)[Location]

            CALCULATE(SUM('Consolidated (2)'[Amount Invoiced (USD)]),

                      TREATAS({"Hong Kong", "Singapore", "Shanghai"}, 'Consolidated (2)'[Location])),

        SelectedCurrency = "HKD",

            CALCULATE(SUM('Consolidated (2)'[Amount Invoiced (USD)]),

                      TREATAS({"Hong Kong"}, 'Consolidated (2)'[Location])),

        SelectedCurrency = "SGD",

            CALCULATE(SUM('Consolidated (2)'[Amount Invoiced (USD)]),

                      TREATAS({"Singapore"}, 'Consolidated (2)'[Location])),

        SelectedCurrency = "CNY",

            CALCULATE(SUM('Consolidated (2)'[Amount Invoiced (USD)]),

                      TREATAS({"Shanghai"}, 'Consolidated (2)'[Location])),

        BLANK()

    )

RETURN

    Amount

I try to group them up and get a Top 5 client list and $$. All works EXCEPT when I filter "Group", does not show data at all. 

Question 2: 
Can I have a 5 year trends of 2024 client list trends? When I click on Top 5, I believe it will count on Total clients and provide me a 5 years trends.

Question 3. I have a line and cluster chart as below:
My line chart show as a dot instead of line, I understand that I might have filter the year of "2024" and the Excel only have 1 data. What can I do to make it to a straight line for more clear vision?

[AmiraBedh]

Question 1 :

I think that the issue is how the TREATAS function is used for "Group" because it is attempting to filter the Location column in Consolidated (2) for "Hong Kong", "Singapore", and "Shanghai". However, when "Group" is selected, the TREATAS might not properly evaluate the aggregated locations.

You need to handle the "Group" selection explicitly by summing all locations without filtering:

SelectedCurrency = "USD" && SELECTEDVALUE(Revenue[Location]) = "Group",
CALCULATE(
    SUM('Consolidated (2)'[Amount Invoiced (USD)])
),

Updated DAX Measure:

consol Amount =
VAR SelectedCurrency =
    SWITCH(
        TRUE(),
        SELECTEDVALUE(Revenue[Location]) = "Group" || ISBLANK(SELECTEDVALUE(Revenue[Location])), "USD",
        SELECTEDVALUE(Revenue[Location]) = "Hong Kong", "HKD",
        SELECTEDVALUE(Revenue[Location]) = "Singapore", "SGD",
        SELECTEDVALUE(Revenue[Location]) = "Shanghai", "CNY",
        BLANK()
    )

VAR Amount =
    SWITCH(
        TRUE(),
        SelectedCurrency = "USD" && SELECTEDVALUE(Revenue[Location]) = "Group",
            CALCULATE(
                SUM('Consolidated (2)'[Amount Invoiced (USD)])
            ),
        SelectedCurrency = "USD",
            CALCULATE(
                SUM('Consolidated (2)'[Amount Invoiced (USD)]),
                TREATAS({"Hong Kong", "Singapore", "Shanghai"}, 'Consolidated (2)'[Location])
            ),
        SelectedCurrency = "HKD",
            CALCULATE(SUM('Consolidated (2)'[Amount Invoiced (USD)]),
                      TREATAS({"Hong Kong"}, 'Consolidated (2)'[Location])),
        SelectedCurrency = "SGD",
            CALCULATE(SUM('Consolidated (2)'[Amount Invoiced (USD)]),
                      TREATAS({"Singapore"}, 'Consolidated (2)'[Location])),
        SelectedCurrency = "CNY",
            CALCULATE(SUM('Consolidated (2)'[Amount Invoiced (USD)]),
                      TREATAS({"Shanghai"}, 'Consolidated (2)'[Location])),
        BLANK()
    )
RETURN
    Amount

---

Question 2: 

Create a measure to dynamically calculate the top 5 clients based on total revenue:

Top 5 Clients Revenue =
CALCULATE(
    [consol Amount], -- Assuming this is the main measure
    TOPN(5, VALUES('Clients'[Client Name]), [consol Amount], DESC)
)

Then create a Line Chart for 5-Year Trends.

---

Question 3:

This happens because you only have one data point for the year "2024."

You can add dummy data for example, you can add rows with zero values for each month or quarter.

or you modify your measure to fill gaps with previous data:

Fill Gaps Measure =
IF(
    ISBLANK([consol Amount]),
    CALCULATE(
        [consol Amount],
        FILTER(
            ALL('Date'),
            'Date'[Date] < MAX('Date'[Date])
        )
    ),
    [consol Amount]
)

If adding data points isn't possible, you can use a "smoothed" line chart or consider a different visualization that does not depend on continuous data (like a bar chart).

[YYlee]

Thank you. 
Q1 solved. 
Q2 supposed based on Q1+Year 2024 only = Top 5 list. 
their trend suppose shown from 2020-2024
Q3. May I ask what is a dummy data? I have 4 categories in Year 2024, and they share the same amount of line y-axis based on location. 
For eg, Group is 40%, Hong Kong is 27%, Shanghai ... Singapore ...But they are not different by the y-axis categories

[AmiraBedh]

Can you clarify more your questions regarding Q2 and Q3? if possible can you share some data please?