Column with data type issue

[Anonymous]

Hi ,

I am trying to create one calculated column Calculated amount. I have a flag column of A and B records, If I select A from slicer it should give sum(Amount), if I select B it should should NA statically for all the ID's and also it should show summation of calculated amount for A flag in table chart and in card also it should give sum(Amount) when i select A, snd NA for B

Please Help

Thanks

[connect · Accepted solution]

Hi,

I understand what you're trying to achieve. Essentially, you need a Calculated Amount column that behaves differently based on the value selected from a slicer for your flag column. Here’s how you can approach this in Power BI using DAX.

To achieve your requirement, you’ll need to:

1. Create a calculated column that gives the correct value depending on the slicer selection.
2. Handle different scenarios for visualization in a Table Chart and a Card.

Here’s a step-by-step solution for your case:

Step 1: Create a New Calculated Column

You need to create a calculated column that returns Amount if the flag is A, and NA if the flag is B.

CalculatedAmount =
VAR SelectedFlag = SELECTEDVALUE('Table'[Flag])
RETURN
IF (
SelectedFlag = "A",
'Table'[Amount],
BLANK()
)

Step 2: Create a Measure for Your Card and Table Visuals

To show the correct value in the table or a card, we need to create a measure that dynamically handles what is displayed based on the slicer selection.

CalculatedAmountMeasure =
VAR SelectedFlag = SELECTEDVALUE('Table'[Flag])
RETURN
SWITCH (
TRUE(),
SelectedFlag = "A", SUM('Table'[Amount]),
SelectedFlag = "B", "NA",
BLANK()
)

Step 3: Using This in Visuals

• Table Visual: Add the CalculatedAmount column. This will show either the Amount value for flag A or BLANK() (which will be visually represented as an empty field for flag B).
• Card Visual: Use the CalculatedAmountMeasure for the card visual. This will dynamically change based on the slicer selection and show the correct aggregation or NA for flag B.

Good luck my friend!!

[Bibiano_Geraldo · Accepted solution]

Hi @Anonymous ,

First create a measure for calculated Amount by the code:

Calculated Amount = 
IF (
    SELECTEDVALUE('YourTable'[Flag]) = "A",
    SUM('YourTable'[Amount]),
    BLANK()
)

And then create a measure to handle "NA" for Flag B by the code:

Display Amount =
IF (
    SELECTEDVALUE('YourTable'[Flag]) = "B",
    "NA",
    [Calculated Amount]
)

This approach ensures that the measure responds dynamically to the slicer selection and that "NA" displays only when "B" is selected. Let me know if you run into any issues with this!

If this help you, please give a kudo and mark as solution

[Anonymous]

Can we show Sum(Calculated Amount) as a summarized amount in card and also table visual . For example in below visual it's not showing summarized amount at bottom when nothing is selected

and in card also it's giving blank when nothing is selected , I want to see sum(calculated amount) when nothing is selected 

Plaese reply back

Thanks

[saurabhtd]

@Anonymous I think calculated columns might not be needed for your need. You can create measures instead.

For card visual you can create this measure

Measure1 = IF(ISFILTERED(A),sum(Amount), IF(ISFILTERED(B),"NA",sum(Amount)))

For table visual you can eighter above or use below logic

Measure2 = IF(HASONEVALUE(A),sum(Amount), IF(HASONEVALUE(B),"NA",sum(Amount)))

[connect · Accepted solution]

Hi,

I understand what you're trying to achieve. Essentially, you need a Calculated Amount column that behaves differently based on the value selected from a slicer for your flag column. Here’s how you can approach this in Power BI using DAX.

To achieve your requirement, you’ll need to:

1. Create a calculated column that gives the correct value depending on the slicer selection.
2. Handle different scenarios for visualization in a Table Chart and a Card.

Here’s a step-by-step solution for your case:

Step 1: Create a New Calculated Column

You need to create a calculated column that returns Amount if the flag is A, and NA if the flag is B.

CalculatedAmount =
VAR SelectedFlag = SELECTEDVALUE('Table'[Flag])
RETURN
IF (
SelectedFlag = "A",
'Table'[Amount],
BLANK()
)

Step 2: Create a Measure for Your Card and Table Visuals

To show the correct value in the table or a card, we need to create a measure that dynamically handles what is displayed based on the slicer selection.

CalculatedAmountMeasure =
VAR SelectedFlag = SELECTEDVALUE('Table'[Flag])
RETURN
SWITCH (
TRUE(),
SelectedFlag = "A", SUM('Table'[Amount]),
SelectedFlag = "B", "NA",
BLANK()
)

Step 3: Using This in Visuals

• Table Visual: Add the CalculatedAmount column. This will show either the Amount value for flag A or BLANK() (which will be visually represented as an empty field for flag B).
• Card Visual: Use the CalculatedAmountMeasure for the card visual. This will dynamically change based on the slicer selection and show the correct aggregation or NA for flag B.

Good luck my friend!!