# DAX – Adjust the Total value – Conditional Total in DAX

### DAX – Adjust the Total value – Conditional Total in DAX

In this article, I want to show you how I achieved a business solution using DAX. Let me explain the requirement.

I have a below table and customer wants the data in the below format.

 Country Color Quarter Product India Green 2019-Q1 P1 USA Blue 2019-Q1 P2 UK Red 2019-Q1 P3 India Red 2019-Q2 P1 USA Blue 2019-Q2 P2 UK Amber 2019-Q2 P4

Expected Solution

 Country 2019-Q1(Green & Blue) 2019-Q2 (Green & Blue) India 100% 0% USA 100% 100% UK 0% 0% Total 67% 33%

Actually, the customer wants to show the count as 1 or 0 if green or blue exists on each country so the required result would be like below.

 Country 2019-Q1(Green & Blue) 2019-Q2 (Green & Blue) India 1 0 USA 1 1 UK 0 0

First step: we can target blue and green count.

Green_Blue = CALCULATE(

COUNTROWS(‘Product’),

‘Product'[Color] in {“Blue”,”Green”})

The next upgraded version is handling blank with zero.

GB =

Var GBVal = CALCULATE(COUNTROWS(‘Product’),’Product'[Color] in {“Blue”,”Green”})

Return

if(GBVal = BLANK(),

0,

GBVal)

Next level, we can divide the value to get the percentage.

GB =

Var GBVal  = CALCULATE(COUNTROWS(‘Product’),

‘Product'[Color] in {“Blue”,”Green”})

Return

if(GBVal  = BLANK(),

0,

GBVal  / CALCULATE(

COUNTROWS(‘Product’),’Product'[Color] in {“Blue”,”Green”},

all(‘Product'[Quarter])))

Then finally, we need to tweak the result on the divide section to get the expected result. This will adjust the total section.

GB =

Var GBVal = CALCULATE(COUNTROWS(‘Product’),

‘Product'[Color] in {“Blue”,”Green”})

Return

if(GBVal = BLANK(),

0,

GBVal / if(

DISTINCTCOUNT(‘Product'[Country])>1,

CALCULATE(COUNTROWS(‘Product’),’Product'[Color] in {“Blue”,”Green”},

all(‘Product'[Quarter])),GBVal))