Forum Discussion

abhifx's avatar
abhifx
Frequent Visitor
2 years ago

Unable to Replace Column data in Matrix Using Switch

Dear All   I have been trying to replicate the revenue statement in Power BI directly linked with a trial balance. However once i insert a format and link multi level data into Power BI, I am unabl...
Need Help
abhifx's avatar
abhifx
Frequent Visitor
2 years ago

Dear danextian , Nailed it, it makes sense too. Thanks for going through my Power BI jungle and find out the issue.

 

As for the alternative suggestion, I still need to do all this manually only as this format is very rigid. But that is ok, Just a one time activity. 

 

I have an additional query if you don't mind, I need to do two more things.

 

1) I want to divide each line by Total sales to get the percentage of sales as a separate column.

2) Subtract the current month from the previous month and also get the percentage variance.

danextian's avatar
danextian
Super User
2 years ago

The new Total Sales formula will give you the Total Sales value regardless of the filter applied to 'MIS Format' table so you can just use it as the denominator to get the % of sales. I would change the flow of relationship between 'MIS Format' and 'Database for MIS' to single direction though (from the former to latter)  so the latter doesn't filter the former. 

Getting the previous month's value would have been simple if you used a separate Dates table. Your month column doesn't mention which year they belong to but assuming they're from the same year, you can create a calculated table of months.

Months = 
VAR __NUMBER =
    GENERATESERIES ( 1, 12, 1 )
RETURN
    ADDCOLUMNS (
        ADDCOLUMNS ( __NUMBER, "Date", DATE ( 2024, [Value], 1 ) ),
        "Month", FORMAT ( [Date], "mmm" )
    )

Then create a one to many relationshiop from Months[Month] to Trial[Month]. You can then use PREVIOUSMONTH to get the previous month's value. Sample formula:

PreviousMonth =
CALCULATE ( [MyMeasure], PREVIOUSMONTH ( Months[Date] ) )

The difference should be simply

=
[MyMeasure] - [PreviousMonth]