cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yamunakosireddi Regular Visitor
Regular Visitor

Create a new table from existing table with calculated measures

Hi Community,

 

I need help with creating another table from the existing table with measures calculated with few conditions.

 

I have a dataset with 10 Years for 2 companies and calculated measure (Value_cal)

Attached the sample data

https://drive.google.com/open?id=1Ie4DUNFy5dZb5H8xIoxJI0AcLiaH9rRa 

Now I want to create another table from an existing table  with below filters

Company =" Company1" and month, year of the data set=2019- Jan and summarize calculated measure with respect to each product

Expected table

CompanyProductDateValue_cal
Company1Product12019-01-014
Company2Product22019-01-014
Company3Product32019-01-01 

 

 

 

 

Any help would be  great full

Thanks

 

2 REPLIES 2
Mariusz Senior Member
Senior Member

Re: Create a new table from existing table with calculated measures

Hi @yamunakosireddi 

Please see the attached Table Expression.

 

YourNewTable = 
ADDCOLUMNS(
    GROUPBY(
        FILTER(
            ADDCOLUMNS(
                YourTable,
                "YearMonth", 
                    VAR d = YourTable[Date]
                    RETURN YEAR( d ) * 100 + MONTH( d )
            ),
            YourTable[Company] = "Company1"
            && [YearMonth] = 201901
        ),
        YourTable[Company],
        YourTable[Product],
        [YearMonth]
    ),
    "Value_cal", CALCULATE( SUM( YourTable[Value_cal] ) ) -- or replace Calculate with your Measure 
)   

Regards,
Mariusz

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

yamunakosireddi Regular Visitor
Regular Visitor

Re: Create a new table from existing table with calculated measures

@Mariusz 

Thanks for the reply,

In my original dataset,

To calculate the final Value, I have Fixed the values with respect to year and each category and also used all except functions.

As per the above solution, it was giving wrong values 

 

Any solution would be grateful

 

Thanks

Yamuna