Forum Discussion

Gintare_D's avatar
Gintare_D
Copper Contributor
May 21, 2022

DAX: conditional measure in Power Pivot column

Dears, 

 

I am wondering if there are expects that would be able to solve this, at a first glance simple, task.

 

I need Power Pivot with Type of discount and actual %, seems simple. Drop an eye to the picture.

But here is the trick: discounts are calculated from different bases, so it's how management wants to see them in the report. 

- Loyalty discount is calculated from initial price. 

- The rest of discount types applies on the price already reduced by loyalty discount.

 

Meaning that in Loyalty discount line I need measure to divide discount by initial price and in other discount lines (many types, dynamic list) - I need measure to divide by price already reduced with loyalty discount. Grand total should be total discounts/gross revenue.

 

I have all data I need, but I struggle to make this column conditional in selecting which measure to calculate based on, let's say external pivot filter/cell on the left of the report.

 

I did not consider putting separate columns as many numbers will not make sense and there is many other columns to fit near by. 

 

Any thoughts how to play this around?

 

Thanks for your expertise in advance!

Gintare

 

 

BI & Data Analysis
power bi
  • flexyourdata's avatar
    flexyourdata
    Iron Contributor
    May 22, 2022

    Gintare_D 

     

    If I have understood correctly, you are not concerned about how to create the calculation, but how to integrate the parameter, which should be selected or entered somewhere on a sheet in the workbook. 

     

    If that's the case, then you can create a named cell which contains the parameter:

     

    Here, I have named B2 "discount_type":

     

     

    After this, I can create a new query by using Data>Get & Transform Data>Get Data>From other Sources>Blank Query:

     

     

    You can put this code in the Advanced Editor:

    let
    Source = Excel.CurrentWorkbook(){[Name="discount_type"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "discount_type"}})
in
    #"Renamed Columns"

     

    This will give you a query you can then use Home>Close & Load to>(Only create connection + Add this data to the data model)

     

     

    Now you will have a new table in the Data Model with a single cell. You can refer to this discount type in your formula to calculate the discounted amount or whatever you need to do. 

     

    Without more details about your existing data, it is difficult to offer more advice on the exact DAX to use in the measure or calculated column.

Resources