Nested if statement with a measure that is summarised

[GS76]

I have a live connection to a Power BI dataset and I need to create a measure that uses an if statement on a measure that is a sum.

I have a fact table with the sales data which contains volume by customer by sales rep by date. This fact table is joined to a customer dimension table, a universal date table and a organisation dimension table. 

I want to do an if statement that says

IF([Volume]<200,"<200",IF(AND([Volume]>=200,[Volume]<=500),"200-500",IF(AND([Volume]>500,[Volume]<=2000),"500-2000","2000+")))

The volume in the if statement needs to be the summed volume when grouped by customer group from the customer dim table and by the area from the organisation dim table. I am not sure how to achieve the sum part and use it in the if statement. I tried initially with creating a new summarised table which worked but then when I publish it, it says it won't refresh because I am using it with direct queries. Can anyone recommend the best way to achieve this.

[Anonymous · Accepted solution]

Hi @GS76 ,

I modified my dax expression according to your request.
Please follow these steps:

1. Use the following DAX expression to create a table

Table = SUMMARIZE('Sales','Sales'[CustomerType],'Sales'[Area],"TotalSales",SUM(Sales[Amount]))

2. Use the following DAX expression to create a measure named ‘Vol Split’ in ‘Table’

Vol Split = IF([TotalSales] < 200 ,"<200",
IF([TotalSales] >=200 && [TotalSales] <=500 , "200-500",
IF([TotalSales] > 500 && [TotalSales] <=2000 ,"500-2000","2000+")))

3. Fianl output

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

[GS76]

Thanks, this works to an extent, however I want the final table above to be a matrix table with area, the volume split and then the total of other measures say total sales for the volume split.

i.e. 

Area	Vol Split	Total Sales
UK	<200	5000
USA	<200	2000
UK	200-500	1000

If I remove the customer type from the table I just end up with the vol split saying 2000+ because it's aggregated it at area level not customer type level.

[Anonymous · Accepted solution]

Hi @GS76 ,

I modified my dax expression according to your request.
Please follow these steps:

1. Use the following DAX expression to create a table

Table = SUMMARIZE('Sales','Sales'[CustomerType],'Sales'[Area],"TotalSales",SUM(Sales[Amount]))

2. Use the following DAX expression to create a measure named ‘Vol Split’ in ‘Table’

Vol Split = IF([TotalSales] < 200 ,"<200",
IF([TotalSales] >=200 && [TotalSales] <=500 , "200-500",
IF([TotalSales] > 500 && [TotalSales] <=2000 ,"500-2000","2000+")))

3. Fianl output

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

[GS76 · Accepted solution]

Thanks, this was my initial solution to the problem and it worked great on my desktop but when I published the report it doesn't refresh because I am using direct query to connect to the rest of the dataset. I've tried importing the model instead but the model is too complex to import so I'm stuck.

[Anonymous]

Hi @GS76 ,

Regarding your new question, after you publish your report to the Power BI Service, either direct query connection mode or import connection mode, you need to configure the on-premises data gateway to enable the refresh feature. For details, please refer to the following article:
What is an on-premises data gateway? | Microsoft Learn

Install an on-premises data gateway | Microsoft Learn

Use the on-premises data gateway app | Microsoft Learn

You will also need to configure data source definitions in Power BI Service as described in the following article:

Add or remove a gateway data source - Power BI | Microsoft Learn

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

[GS76]

Thanks the issue was I'd connected to the dataset first with a direct query and then tried to add a table when this is published to the server the refresh is not supported. I don't think it's related to the gateway set up as everything I google says you cannot use a summary table with a direct query. To get around it I had to just import the table I needed from the dataset and apply the summarised query to it when importing. 

Thank you for all your help. It got me to the solution.

[Anonymous]

Hi @GS76 ,

I've assumed some data. The table data is shown below:
Fact table 'Sales'

Dimension table 'Date'

Dimension table 'Customer'

Dimension table 'Organization'

Please follow these steps:
1. Use the following DAX expression to create a measure named ‘TotalVolume’

TotalVolume = 
VAR _a = SUMX(GROUPBY('Sales',Customer[Type],Sales[Area]),CALCULATE(SUM(Sales[Amount])))
RETURN
IF( _a < 200,"<200",
IF(_a >=200 && _a <= 500 , "200-500",
IF(_a > 500 && _a <= 2000,"500-2000","2000+")))

2. Final output

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