How to display 0 percent of grand total

[Vishnoo]

I have the below model and two measures. Both table have a one to Many relationship.

Fruits Support Table

Fruits Table

FruitsSold 2018 = (CALCULATE(SUM(Fruits[Qty]), Fruits[Sold Year] = 2018)) +0

FruitsSold 2018 = (CALCULATE(SUM(Fruits[Qty]), Fruits[Sold Year] = 2018)) +0

Here is my visual.

Now, when i apply a filter on Name, the % of grand total is coming blank. I want to display them as Zero like how it displays when it is not filterred. How to acheive this. Thanks.

[v-xicai · Accepted solution]

Hi Vishnoo,

It’s not supported to make some setting directly to display the %GT FruitSold 2019 column or %GT FruitSold 2018 column as "0.00%" to interact some selections in slicer, when you create the %GT FruitSold 2019 as the way in picture below.

 

To work around the issue, you can create some columns and measures to implement your demand.

From your description, I create sample data to reproduce the scenario. Firstly, create columns TotalSold 2018 and TotalSold 2019 to get total fruit Qty for each year.

TotalSold 2018 = CALCULATE(SUM(Fruits[Qty]),FILTER(Fruits,Fruits[Sold Year]=2018))

TotalSold 2019 = CALCULATE(SUM(Fruits[Qty]),FILTER(Fruits,Fruits[Sold Year]=2019))

Create measures %GT FruitSold 2018 and %GT FruitSold 2019 to get percentage of each fruit in total fruit Qty for each year.

%GT FruitSold 2018 = VALUE(DIVIDE([FruitsSold 2018],MAX(Fruits[TotalSold 2018])))

%GT FruitSold 2019 = VALUE(DIVIDE([FruitsSold 2019],MAX(Fruits[TotalSold 2019])))

Then, choose the columns %GT FruitSold 2018 and %GT FruitSold 2019 ,and click "%" setting 2 decimal under Modeling ribbon,

 

Finally, when you make some selections in slicer, return the result showing below picture.

 

Here is my test pbix file link: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EYQiQwA7gIlOpM6Tlv...

Best Regards,

Amy

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