Percentage decimal format and Sum in total row

[SamVH12]

Hi,

I want to create a visual as a table like below:

Slicer: region

Customer number	Customer name	Revenue achievement	Quant Achievement	Passed
1002	A	OK	Not OK	2.5%
2871	B	OK	OK	4%
3635	C	Not OK	Not OK	0%
Total				6.5%

The columns "Revenue achievement" and "Quant achievement" are measures to check if the actual revenue/quantity is over the planned revenue/quantity or not.

The "Passed" column is another measure that I created using DAX:

Passed =
var _Revenue = IF([Revenue Achievement] = "OK", 2.5, 0)
var _Quant = IF([Quant Achievement] = "OK", 1.5, 0)
var total = _Revenue + _Quant
var percentage = total / 100
RETURN
IF( ISINSCOPE('FactRevenue'[Customer Number]), CALCULATE(SUMX(ALLSELECTED('FactRevenue'[Customer Number]), percentage)), percentage )

I changed the format of the "Passed" measure to #.#%, but the decimal places shown in my table are not as expected; it shows 2.5% and 4.%. I want the table to show dynamic decimal places, such as 2.5% and 4%. (I have already solved this one)

And I want to show the total of the "Passed" column which is 6.5%, and hide the totals for "Revenue achievement" and "Quant achievement", but using my DAX , it's not adapting for the 'Passed' column.

Could someone help me resolve this? Thanks in advance!

[suparnababu8]

Hi @SamVH12 

1. Dynamic Decimal Places for the “Passed” Column

To ensure the “Passed” column shows dynamic decimal places, you can use the FORMAT function in DAX. Modify your Passed measure as follows:

Passed =
VAR _Revenue = IF([Revenue Achievement] = "OK", 2.5, 0)
VAR _Quant = IF([Quant Achievement] = "OK", 1.5, 0)
VAR total = _Revenue + _Quant
VAR percentage = total / 100
RETURN
IF(
    ISINSCOPE('FactRevenue'[Customer Number]),
    FORMAT(CALCULATE(SUMX(ALLSELECTED('FactRevenue'[Customer Number]), percentage)), "#.#%"),
    FORMAT(percentage, "#.#%")
)

2. Showing Total for the “Passed” Column and Hiding Totals for Other Columns

To show the total for the “Passed” column and hide the totals for “Revenue achievement” and “Quant achievement,” you can use the following approach:

1. Create a measure for the total of the “Passed” column:
   

TotalPassed =
SUMX(
    SUMMARIZE(
        'FactRevenue',
        'FactRevenue'[Customer Number],
        "PassedValue", [Passed]
    ),
    [PassedValue]
)

• Modify your table visual:

  • Add the Passed measure to your table.
  • Add the TotalPassed measure to your table and place it in the “Values” section.
  • Hide the totals for “Revenue achievement” and “Quant achievement” by setting their totals to blank.Here’s how you can set the totals to blank:
    
    

RevenueAchievementTotal = 
IF(
    HASONEVALUE('FactRevenue'[Customer Number]),
    [Revenue Achievement],
    BLANK()
)

QuantAchievementTotal = 
IF(
    HASONEVALUE('FactRevenue'[Customer Number]),
    [Quant Achievement],
    BLANK()
)
​

Putting It All Together

1. Create the slicer for the region.
2. Add the table visual with the following fields:
   • Customer number
   • Customer name
   • RevenueAchievementTotal
   • QuantAchievementTotal
   • Passed
   • TotalPassed

This setup should give you the desired table visual with dynamic decimal places for the “Passed” column and the correct totals.

Give this a try and let me know if it works for you! 😊

[SamVH12]

I think you are misunderstanding my needs. I want to show a table exactly as I have shown in my post, with the total of "Passed" in the last row, not a separate total column in the table. Also, I have already fixed the decimal format.

[v-linyulu-msft · Accepted solution]

Thanks for the reply from PijushRoy and suparnababu8  , please allow me to provide another insight:

Hi, @SamVH12 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.Make the following changes to the visualization, in particular, change the aggregation method of the Customer number column to no aggregation. 

3.Here's my final result, which I hope meets your requirements.

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.
 

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PijushRoy]

Hi @SamVH12 

In place of format #.#%, use ##.00% or #.0%
Let me know if solved 
Thanks

[SamVH12]

There was a bug in my table, I deleted it and added the Passed measure again. The format #.#% is now correct.

Now, I need to check the sum in the total row